CREATE TRIGGER tr_T_A ON T_A for INSERT,UPDATE,DELETE 如IF exists (select * from inserted) and not exists (select * from deleted) 则为 INSERT 如IF exists(select * from inserted ) and exists (select * from deleted) 则为 UPDATE 如IF exists (select * from deleted) and not exists (s...
在对表进行操作时,总会产生 INSERTED 和(或)DELETED表,不管这个操作是否已经进行。这里的和/或,要看进行的什么操作,插入,产生 INSERTED 表,删除,产生DELETED表,而UPDATE则两个都会产生。 1.触发器的插入操作: create trigger tri_buy_insert on buyafter insertasinsert into detail(buyid,name,kind,oldcost,new...
SELECT * FROM inserted I INNER JOIN CommodityInfo C ON I.CommodityId=C.CommodityId WHERE I.Amount>C.Amount ) BEGIN ROLLBACK --后触发器 PRINT '商品的销售量大于商品的库存量' END ELSE BEGIN UPDATE CommodityInfo SET Amount=Amount-(SELECT Amount FROM inserted) WHERE CommodityId IN ( SELECT Com...
其步骤就是:利用update操作触发器产生的2个虚拟表【inserted】用来存储修改的数据信息和【deleted】表,然后将对应的数据更新到对应数据表中的字段信息中; 1.首先创建3个表: a.信息表: 1 USE [SQL-LI] 2 BEGIN TRANSACTION CHUANGJIAN_XINXIN_TAB 3 --创建命名为【XINXIN_TAB】的数据表,同时不允许字段为空 4...
一个触发器内三种INSERT,UPDATE,DELETE状态 CREATE TRIGGER tr_T_A ON T_A for INSERT,UPDATE,DELETE 如IF exists (select * from inserted) and not exists (select * from deleted) 则为 INSERT 如IF exists(select * from inserted ) and exists (select * from deleted) 则为 UPDATE ...
一个触发器内三种INSERT,UPDATE,DELETE状态 CREATE TRIGGER tr_T_A ON T_A for INSERT,UPDATE,DELETE 如IF exists (select * from inserted) and not exists (select * from deleted) 则为 INSERT 如IF exists(select * from inserted ) and exists (select * from deleted) 则为 UPDATE ...
⼀个触发器内三种INSERT,UPDATE,DELETE状态 CREATE TRIGGER tr_T_A ON T_A for INSERT,UPDATE,DELETE 如IF exists (select * from inserted) and not exists (select * from deleted) 则为 INSERT 如IF exists(select * from inse...
判断deleted表是否有数据,有就是update,否则就是insert Deleted表是修改前的数据,inserted是新增和修改后的数据
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) begin -- 更新触发. select @pid = id from T2; update inserted set pid = @pid where id = @id print '修改T1成功!'; end;end现在的需求是,触发器在isnert时触发,触发的同时 insert T1 ,update T2,这两个操作前后同时完成...
update dbo.test1 set name=inserted.name,sex=inserted.sex,age=inserted.age,classid=inserted.classid,height=inserted.height, xml1=inserted.xml1,xml2=inserted.xml2 from inserted where dbo.test1.id=(select top 1 id from deleted order by id desc); ...