CREATE Trigger ChooseValue On Test1
For Update,Insert
As
if exists(select * from deleted)
update test1 set x4 = "4" from test1, inserted where test1.x1 = inserted.x1 And test1.x4=inserted.x4
else
update test1 set x4 = "9" from test1, inserted where test1.x1 = inserted.x1 And test1.x4=inserted.x4
CREATE Trigger ChooseValue On Test1
For Update,Insert
As
if exists(select * from deleted)
update test1 set x4 = "4" from test1 t, inserted i where t.x1 = i.x1
else
update test1 set x4 = "9" from test1 t, inserted i where t.x1 = i.x1
如下:
CREATE TRIGGER [Update Employee] ON dbo.Employee
FOR UPDATE
AS
Update Salary Set Salary.ID=Inserted.ID,
Salary.DepID=Inserted.DepID,
Salary.Name=Inserted.Name,Salary.Status=Inserted.Status From Inserted,deleted
Where deleted.DepID=salary.depid and deleted.id=salary.id
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an INSERT or UPDATE transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
An UPDATE transaction is like a delete followed by an insert; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
在表更新或删除的时候会生成一个系统表,你要在触发器里读取这个系统表,
如你删除时建DELETEd表,添加时在INSERTed表内写入数据
一个典型的例子。
creat trigger delete_artists
on Recordings
for delete as
begin
delete Artists from artists,deleted
where artist.artist_id = deleted.artist_id
end
go