Question

Sql Server 触发器问题，触发器触发的条件有 insert,update,delete ,我想要一个例子 在一个表建立一个触

触发器触发的条件有 insert,update,delete ,我想要一个例子 在一个表建立一个触发器在所有条件都会触发，当新增时 执行同步 insert 到另外一张表，update 时更新，delete 时也对应删除
例如表a,b
create table a (a varchar(10));

create table b (a varchar(10));

create trigger tr_a
on a
for insert,update,delere as
begin

end
请帮忙将上面代码补充完整，谢！
除了inserted 表跟deleted表有没有其他方法可以知道当时是执行哪类操作么？
如oracle 里面用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作可以判断当时执行什么操作写对应sql

Answer 1

create trigger tr_a
on a
for insert,update,delere as
begin

IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
BEGIN
-- 插入触发.
INSERT INTO B
SELECT
inserted.a
FROM
inserted;
END;

IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
-- 更新触发.
DELETE FROM B WHERE a in ( SELECT a FROM deleted);

INSERT INTO B
SELECT
inserted.a
FROM
inserted;
END;

IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
-- 删除触发
DELETE FROM B WHERE a in ( SELECT a FROM deleted);

END;

end

Answer 2

declare @tmp varchar(10)
select @temp=a from inserted
insert b values(@tmp)