create trigger basic_update on t_basic_pro for update as if @@rowcount = 0 return if update(mount) begin update t_basic set mount = inserted. mount from inserted,deleted where ID = deleted.ID and inserted.ID = deleted.ID if @@error <> 0 goto error_handler end return error_handler: begin rollback transaction return end
我的例子是这样,你自己看看吧 test7上的数据随test1变 create trigger trig_test4 on test1 for insert,delete , update as begin delete from test7 from deleted where test7.a = deleted.a insert into test7 select * from inserted where not exists ( select * from test7 where test7.a = inserted. a) end go
看看一个实际例子: CREATE TRIGGER tr_update_xtrybm ON xtrybm FOR UPDATE AS BEGIN declare @tran_serial decimal(18,0) declare @tran_xtrybm char(10) declare @tran_xtryxm char(16) declare @tran_xtrymm char(28) declare @tran_xtrycd decimal(2, 0) declare @str_xtrybm varchar(254)--define cursor for deleted declare xtrybm_deleted_cursor CURSOR for select xtrybm from deleted--Open the cursor and scroll it. open xtrybm_deleted_cursor fetch xtrybm_deleted_cursor into @tran_xtrybm while (@@sqlstatus<>2) BEGIN select @tran_serial = isnull(max(tran_sn), 0) from tran_log select @tran_serial = @tran_serial + 1 if ltrim(rtrim(@tran_xtrybm)) is null select @str_xtrybm = "" else select @str_xtrybm = " trim(xtrybm) = '" + convert(varchar(254),@tran_xtrybm) + "' and " insert into tran_log (tran_sn,tran_date,sql_0, sql_1 ) values (@tran_serial, getdate(), "delete xtrybm where ", @str_xtrybm ) fetch xtrybm_deleted_cursor into @tran_xtrybm END close xtrybm_deleted_cursor --define cursor for inserted declare xtrybm_inserted_cursor CURSOR for select xtrybm, xtryxm, xtrymm, xtrycd from inserted--Open the cursor and scroll it. open xtrybm_inserted_cursor fetch xtrybm_inserted_cursor into @tran_xtrybm, @tran_xtryxm, @tran_xtrymm, @tran_xtrycd while (@@sqlstatus<>2) BEGIN select @tran_serial = isnull(max(tran_sn), 0) from tran_log select @tran_serial = @tran_serial + 1 insert into tran_log values (@tran_serial, getdate(), "insert into xtrybm values ( ", "'"+convert(varchar(254),@tran_xtrybm) + "',", "'"+convert(varchar(254),@tran_xtryxm) + "',", "'"+convert(varchar(254),@tran_xtrymm) + "',", isnull(convert(varchar(254),@tran_xtrycd), '0') + " )", "") fetch xtrybm_inserted_cursor into @tran_xtrybm, @tran_xtryxm, @tran_xtrymm, @tran_xtrycd END close xtrybm_inserted_cursor ENDgo print ' Trigger tr_update_xtrybm generated success ' go
create trigger basic_update on t_basic_pro for update as if not update(mount) returnupdate t_basic set mount = inserted.mount from inserted,deleted,t_basic where t_basic.ID = deleted.ID and inserted.ID = deleted.ID if @@error <> 0 begin rollback transaction return end end go
on t_basic_pro for update
as
if @@rowcount = 0
return
if update(mount)
begin
update t_basic set mount = inserted. mount from inserted,deleted
where ID = deleted.ID and inserted.ID = deleted.ID
if @@error <> 0
goto error_handler
end
return
error_handler:
begin
rollback transaction
return
end
to:N_chow(一劍飄香),谢谢你的提醒,这真是我没有想到的。有例子给我看看吗?
多筆insert/update時的處理
的例子嗎?
test7上的数据随test1变
create trigger trig_test4
on test1
for insert,delete , update
as
begin
delete from test7 from deleted where test7.a = deleted.a
insert into test7 select * from inserted where not exists ( select * from test7 where test7.a = inserted. a)
end
go
CREATE TRIGGER tr_update_xtrybm ON xtrybm
FOR UPDATE AS
BEGIN
declare @tran_serial decimal(18,0)
declare @tran_xtrybm char(10)
declare @tran_xtryxm char(16)
declare @tran_xtrymm char(28)
declare @tran_xtrycd decimal(2, 0) declare @str_xtrybm varchar(254)--define cursor for deleted
declare xtrybm_deleted_cursor CURSOR for
select xtrybm
from deleted--Open the cursor and scroll it.
open xtrybm_deleted_cursor
fetch xtrybm_deleted_cursor into
@tran_xtrybm
while (@@sqlstatus<>2)
BEGIN
select @tran_serial = isnull(max(tran_sn), 0) from tran_log
select @tran_serial = @tran_serial + 1
if ltrim(rtrim(@tran_xtrybm)) is null select @str_xtrybm = "" else select @str_xtrybm = " trim(xtrybm) = '" + convert(varchar(254),@tran_xtrybm) + "' and "
insert into tran_log (tran_sn,tran_date,sql_0, sql_1 )
values (@tran_serial,
getdate(),
"delete xtrybm where ",
@str_xtrybm )
fetch xtrybm_deleted_cursor into
@tran_xtrybm
END
close xtrybm_deleted_cursor
--define cursor for inserted
declare xtrybm_inserted_cursor CURSOR for
select xtrybm, xtryxm, xtrymm, xtrycd
from inserted--Open the cursor and scroll it.
open xtrybm_inserted_cursor
fetch xtrybm_inserted_cursor into
@tran_xtrybm, @tran_xtryxm, @tran_xtrymm, @tran_xtrycd
while (@@sqlstatus<>2)
BEGIN
select @tran_serial = isnull(max(tran_sn), 0) from tran_log
select @tran_serial = @tran_serial + 1
insert into tran_log
values (@tran_serial,
getdate(),
"insert into xtrybm values ( ",
"'"+convert(varchar(254),@tran_xtrybm) + "',",
"'"+convert(varchar(254),@tran_xtryxm) + "',",
"'"+convert(varchar(254),@tran_xtrymm) + "',",
isnull(convert(varchar(254),@tran_xtrycd), '0') + " )",
"")
fetch xtrybm_inserted_cursor into
@tran_xtrybm, @tran_xtryxm, @tran_xtrymm, @tran_xtrycd
END
close xtrybm_inserted_cursor
ENDgo print ' Trigger tr_update_xtrybm generated success '
go
on t_basic_pro for update
as
if not update(mount)
returnupdate t_basic set mount = inserted.mount
from inserted,deleted,t_basic
where t_basic.ID = deleted.ID
and inserted.ID = deleted.ID
if @@error <> 0
begin
rollback transaction
return
end
end
go