SQLServer触发器的问题，关键字修改了能处理吗

Question

用的SQLServer2005，主要是从Oracle将数据库移植到SQLServer，所以触发器得重写在oracle 中用 old ，new 关键字可以访问修改删除之前的所有字段记录，但是在SQLServer中就不大么容易了，尤其是如果关键字修改了，inserted 和 deleted 不能关联，参照修改之前的记录将新的值存放到关联表中就很难做到了例如  A表 A1，A2，A3（关键字A1）
      B表 B1，B2，B3（关键字B1，B3关联存储A1的值）
      那么在A1被修改了，怎么将B3修改呢？请教高手。

Answer 1

Deleted --> Old
Inserted --> New关联修改，可设计参照Cascade

Answer 2

还是可以想办法将 inserted,deleted关联起来的。CREATE TRIGGER trg_t1 ON A
FOR UPDATE
AS
BEGIN
;
WITH ins
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS rowid,  * FROM inserted
),
del
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS rowid,  * FROM deleted
)
UPDATE b
SET B3=ins.a1
FROM b,ins,del
WHERE b.b3=del.a1
AND ins.rowid=ins.rowid


END

Answer 3

如果update时
inserted 存放的是修改后的值
deleted 存放的是修改前的值

Answer 4

是可以将inserted和deleted关联起来，达到跟Oracle的相同效果。

Answer 5

目前不想用级联的办法，因为级联的话需要设置外键 三楼的  Garnett_KG  能再解释一下rowid 吗？ 直接用deleted.rowid = inserted.rowid  可以吗？

Answer 6

这个rowid 同 oracle 的 rowid 吗

Answer 7

Does this table you are dealing with has a primary key? Do you update the primary key when you update your this table?If the answer is Yes (to primary key) and No (to updating primary key), then it's a straight forward process to find before and after valueselect a.* /*after value*/, b.* /*before value here */ from inserted a inner join deleted b on a.primarykeycol=b.primarykeycol

Answer 8

No, they are not! Oracle rowid is an internal ID that is guaranteed to be deterministic and non-user updateable within a table. That is, you can rely on rowid to compare across different versions of row. In fact, this is one of the primary feature of Oracle database engine that makes it non-blocking - that is read can never block write and write can never block read (and the reason that Oracle database engines more scalable than SQL Server which relies on locking for both read and write).MSDN does not specify the ordering of rows within the virtual inserted and deleted tables and I have come across different ordering depending on actual file location and query execution plan. Hence one cannot rely on an artifiically generated rowid (row_number over certain field) to guarantee the row in deleted and inserted table match unless that certain field is the primary key of the table that does not change across updates. In fact, the example given in #2楼, getdate() value is identical within a single select statement and hence completely useless for the purpose of ordering. It is highly recommended that no assumption be made on the ordering of data within inserted/deleted virtual tables as the ordering may change across service packs, and versions of SQL Server implementations.If you really want to use a before and after value on a table where update may potentially change the primary key value, the only guaranteed method is to use output parameter as per below:create table @audit_table
(oldvalue varchar(10), newvalue varchar(10))
update mytable
set myfield=xxx
output 
deleted.myfield
inserted.myfield
into @audit_tableNote the above only works on SQL2K5/2K8

Answer 9

不一样吧，ms server中的rowid,oracle中的rowid是随机生成二进制码的

Answer 10

row_number()产生的rowid跟oracle的rowid当然不是一回事，实做的原理不一样。
#2楼的order by getdate()的目的就是为了不对结果集排序,从而保持结果集原本scan出来的顺序，但是获取了row_id.你可以说这种方式不是100%的保证inserted,deleted相同记录的row id相同，就像select @x+@x+col顺序累加字串一样，MSDN没有对此做过说明，但测试结果都是正确的。

Answer 11

补充点, INSTEAD OF TRIGGER内 ORDER BY GETDATE() 得到的rowid是肯定没问题的。

Answer 12

If you run select statement from your CTE definition directly (and output it to SSMS), you will find getdate() will return the same value for every row in the deleted and inserted - that is, the getdate() is completely useless for the purpose of ordering!If you ask any MSSQL MVP worth their salt, the response will be DO NOT ASSUME identical ORDERING within the inserted and deleted virtual tables. MSDN does state that @a+@b+@c will form correctly. You obviously haven't read the following excerpt from MSDN http://msdn.microsoft.com/en-us/library/aa276862(SQL.80).aspx. There are equivalent version of this page in MSDN for SQL2K5, SQL2K8, SQL7, SQL6.5 (and most likely in earlier versions but they are no longer online).

Answer 13

1). 你可能没理解我的意思. ORDER BY GETDATE()我就是为了要它completely useless for the purpose of ordering!
    我只是为了得到rowid.
这是order by getdate()的SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) as rowid,* FROM deleted
  |--Sequence Project(DEFINE:([Expr1004]=row_number))
       |--Compute Scalar(DEFINE:([Expr1007]=(1)))
            |--Segment
                 |--Clustered Index Seek(OBJECT:([deleted]), SEEK:([deleted].[Act]=(3)) ORDERED FORWARD)这是order by primary key的
SELECT ROW_NUMBER() OVER(ORDER BY id) as rowid,* FROM deleted
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
            |--Segment
                 |--Sort(ORDER BY:([deleted].[id] ASC))
                      |--Clustered Index Seek(OBJECT:([deleted]), SEEK:([deleted].[Act]=(3)) ORDERED FORWARD)看到order by getdate()没有sort操作了吗？ 它只是对对Clustered Index Seek的返回的结果产生row_id。2).
最开始是我手误,#14楼我想表达的是select @x=@x+col from tb 这个，而不是 @a+@b+c这样。对于@x的结果，是依Allocation order还是 tb的Clustered index order累加，MSDN没有做过说明。

Answer 14

Getdate is a compile time binding according Microsoft - hence you will never get a different value of getdate within a single select statement. Hence order by getdate() will never work as you intend it, whether in an after trigger or instead of trigger.

Answer 15

order by getdate()不是为了排序，说白了是为了符合 row_number()的语法！你看看#17楼的回复。

Answer 16

I get what you are trying to use with getdate() for the purpose of getting rowid - however I do not agree this is the safe way of doing it - given the assumption you are making that clustered index scan will return the records in the same order. I have seen clustered index scan returning resultsets (from the same table) in different order from a stored proc in a MARS-enabled connection environment.

Answer 17

那是另外一个故事了。如果说clustered index scan返回的结果顺序不同，90%的可能性是索引存在大量碎片。你可以Rebuild index然后再试试看。