T-SQL 如何在插入前修改值 [英] T-SQL how to modify the value before insert

问题描述

我发现 sql server 中只有 after 和而不是触发器.并且修改插入的伪表中的值是非法的.然后我的问题出现了:如果我想检查将要插入到我的表中的数据，并且当数据违反我的约束时，我应该将这些值修改为默认值，该怎么做?插入后如何更新值?但是，如果我的表中没有唯一的主键或列，我如何找到刚刚插入的行然后更新它?

I find that there are only after and instead of triggers in sql server. And it is illegal to modify the values in the inserted pesudo table. Then my problem occurs: If I want to check the data which is going to be inserted into my table, and when the data violates my constraints I should modify these values to default values, how to do it ? How about updateing the values after inserted ? However, if there's no primary key or colum which is unique in my table, how can I locate the row just inserted and then update it ?

推荐答案

基本上，使用 INSTEAD OF INSERT 触发器，您可以实现您想要的 - 只需从INSERTED 伪表，修改后插入到表中

Basically, with an INSTEAD OF INSERT trigger, you can achieve what you're looking for - just read out the data from the INSERTED pseudo table, modify it, and insert it into the table

所以你的触发器看起来像这样:

So your trigger would look something like this:

CREATE TRIGGER YourTrigger ON dbo.YourTable    
INSTEAD OF INSERT
AS
    SET NOCOUNT ON

    -- do the INSERT based on the INSERTED pseudo table, modify data as needed
    INSERT INTO dbo.YourTable(Col1, Col2, ....., ColN)
      SELECT 
          Col1, 2 * Col2, ....., N * ColN
      FROM 
          INSERTED

当然，您也可以添加例如以 WHERE 子句的形式检查该 SELECT .... FROM INSERTED 语句到例如忽略某些行 - 可能性无穷无尽！

Of course, you could also add e.g. checks in the form of WHERE clause to that SELECT .... FROM INSERTED statement to e.g. ignore certain rows - the possibilities are endless!

这篇关于T-SQL 如何在插入前修改值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！