本人初学SQL语法,下面是我写的触发器,请教怎样从语句上简化一下。CREATE TRIGGER identical_insert ON ch_mon
instead of INSERT
AS
if @@rowcount=0 return
declare @tbl varchar(64),@recno bigint,@itype varchar(64),@otype varchar(64)
select @tbl=tbl,@recno=recno,@itype=type from inserted
if not exists(select 1 from ch_mon where recno=@recno and tbl=@tbl)
begin
insert into ch_mon select * from inserted
return
end
select @otype=type from ch_mon where recno=@recno and tbl=@tbl
if @otype='insert' and @itype='delete'
begin
delete from ch_mon where recno=@recno and tbl=@tbl
return
end
if @otype='update' and @itype='delete'
update ch_mon set type='delete'where recno=@recno and tbl=@tbl
go
instead of INSERT
AS
if @@rowcount=0 return
declare @tbl varchar(64),@recno bigint,@itype varchar(64),@otype varchar(64)
select @tbl=tbl,@recno=recno,@itype=type from inserted
if not exists(select 1 from ch_mon where recno=@recno and tbl=@tbl)
begin
insert into ch_mon select * from inserted
return
end
select @otype=type from ch_mon where recno=@recno and tbl=@tbl
if @otype='insert' and @itype='delete'
begin
delete from ch_mon where recno=@recno and tbl=@tbl
return
end
if @otype='update' and @itype='delete'
update ch_mon set type='delete'where recno=@recno and tbl=@tbl
go
CREATE TRIGGER identical_insert ON ch_mon
instead of INSERT
AS
Begin
if @@rowcount=0 return
Insert into ch_mon select * from inserted A Where Not Exists((select 1 from ch_mon where recno=A.recno and tbl=A.tbl)
delete A from ch_mon A Inner Join inserted B On A.recno=B.recno And A.tbl=B.tbl where A.type='insert' and B.type='delete'
update A set type='delete' From ch_mon A Inner Join inserted B On A.recno=B.recno And A.tbl=B.tbl where A.type='update' and B.type='delete'
End
GO