Question

SQL数据库建立触发器,在想表中插入数据之前检测有没有相同的记录,有则删除后再插入

SQL server2008数据库, A 表的登记号是主键, 在A表执行删除操作时,同时向B表插入一条相同的记录,
如果B表存在登记号相同的记录时,则先删除B表的该记录再执行输入插入,
这个触发器怎么写?

Answer 1

create trigger [dbo].[delete]
on [dbo].[表A]
instead of delete
as
begin
if not exists (select count(*) from B where 登记号 = 条件)
insert 表B---插入语句
else
delete 表B where 登记号 = 条件
insert 表B ---插入语句
end

Answer 2

假定A、B结构相同，登记号字段名为id。
CREATE TRIGGER TR_delete_A ON [dbo].[A]
FOR DELETE
AS
DECLARE @id NUMERIC
SET @id = (SELECT id FROM deleted)
IF @id IS NOT NULL
BEGIN
delete from b where id=@id
insert into b select * from deleted where id=@id
END

Answer 3

create trigger a_tri_ins
on A
as
begin
declare @c登记号 varchar(100)
select @c登记号 = 登记号 from inserted
if exists ( select 1 from B where 登记号 = @c登记号 )
delete B where 登记号 = @c登记号

insert B ( 列名全写出来 )
select 列名全写出来 from inserted
end