create trigger ti_表 on 表 for insert as select * from inserted GO create trigger tu_表 on 表 for update as select * from inserted select * from deleted GO create trigger td_表 on 表 for delete as select * from deleted
CREATE TRIGGER tg_T_SXHT_update ON dbo.T_SXHT FOR UPDATE AS declare @count int,@c1 varchar(255) declare @ZBBH char(5),@YYBBH int, @HTH char(10),@XZH char(6),@XXZH char(10) declare @ZBBH_o char(5),@YYBBH_o int, @HTH_o char(10),@XZH_o char(6),@XXZH_o char(10) declare @HTJE money,@HBLB char(6),@HTRQ datetime,@FHRQ datetime,@XDRQ datetime declare @HTJE_o money,@HBLB_o char(6),@HTRQ_o datetime,@FHRQ_o datetime,@XDRQ_o datetime declare @AZRQ datetime,@SJRQ datetime,@TXCJ char(20),@XZLB char(8) declare @AZRQ_o datetime,@SJRQ_o datetime,@TXCJ_o char(20),@XZLB_o char(8) declare @XZZT char(6),@XWS int,@JTHTH char(10),@XGRDM char(4) declare @XZZT_o char(6),@XWS_o int,@JTHTH_o char(10) declare @BPSL varchar(10),@BPBS int, @RCST varchar(20),@BUG varchar(20),@SBSJRQ datetime declare @BPSL_o varchar(10),@BPBS_o int, @RCST_o varchar(20),@BUG_o varchar(20),@SBSJRQ_o datetime declare @changekey int select @count = @@rowcount if (@count > 0) BEGIN select @changekey = 0 select @XGRDM = XGRDM from inserted select @ZBBH = ZBBH from inserted select @ZBBH_o = ZBBH from deleted if (@ZBBH is not null) select @c1 = '总部编号='+'"'+rtrim(@ZBBH)+'"("'+rtrim(@ZBBH_o)+'")'select @YYBBH=YYBBH from inserted select @YYBBH_o=YYBBH from deleted if (@YYBBH is not null) select @c1 = @c1+',营业部编号="'+rtrim(@YYBBH)+'"("'+rtrim(@YYBBH_o)+'")'select @HTH=HTH from inserted select @HTH_o=HTH from deleted if (@HTH is not null) select @c1 = @c1+',合同号="'+rtrim(@HTH)+'"("'+rtrim(@HTH_o)+'")' if (UPDATE(ZBBH) and not UPDATE(YYBBH))------我的理解,此情况不会发生。 begin select @changekey=1 update t_sxktzt set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o update t_sxysmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o update t_sxssmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o update t_xw set zbbh=@ZBBH,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o end if UPDATE(YYBBH) begin select @changekey=1 update t_sxktzt set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o update t_sxysmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o update t_sxssmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o if (@YYBBH is not null) update t_xw set zbbh=@ZBBH,yybbh=@YYBBH,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o else update t_xw set zbbh=@ZBBH,yybbh=@YYBBH_o,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o end
CREATE TRIGGER tg_T_SXHT_update ON dbo.T_SXHT FOR UPDATE AS declare @count int,@c1 varchar(255) declare @ZBBH char(5),@YYBBH int, @HTH char(10),@XZH char(6),@XXZH char(10) declare @ZBBH_o char(5),@YYBBH_o int, @HTH_o char(10),@XZH_o char(6),@XXZH_o char(10) declare @HTJE money,@HBLB char(6),@HTRQ datetime,@FHRQ datetime,@XDRQ datetime declare @HTJE_o money,@HBLB_o char(6),@HTRQ_o datetime,@FHRQ_o datetime,@XDRQ_o datetime declare @AZRQ datetime,@SJRQ datetime,@TXCJ char(20),@XZLB char(8) declare @AZRQ_o datetime,@SJRQ_o datetime,@TXCJ_o char(20),@XZLB_o char(8) declare @XZZT char(6),@XWS int,@JTHTH char(10),@XGRDM char(4) declare @XZZT_o char(6),@XWS_o int,@JTHTH_o char(10) declare @BPSL varchar(10),@BPBS int, @RCST varchar(20),@BUG varchar(20),@SBSJRQ datetime declare @BPSL_o varchar(10),@BPBS_o int, @RCST_o varchar(20),@BUG_o varchar(20),@SBSJRQ_o datetime declare @changekey int select @count = @@rowcount if (@count > 0) BEGIN select @changekey = 0 select @XGRDM = XGRDM from inserted select @ZBBH = ZBBH from inserted select @ZBBH_o = ZBBH from deleted if (@ZBBH is not null) select @c1 = '总部编号='+'"'+rtrim(@ZBBH)+'"("'+rtrim(@ZBBH_o)+'")'select @YYBBH=YYBBH from inserted select @YYBBH_o=YYBBH from deleted if (@YYBBH is not null) select @c1 = @c1+',营业部编号="'+rtrim(@YYBBH)+'"("'+rtrim(@YYBBH_o)+'")'select @HTH=HTH from inserted select @HTH_o=HTH from deleted if (@HTH is not null) select @c1 = @c1+',合同号="'+rtrim(@HTH)+'"("'+rtrim(@HTH_o)+'")' if (UPDATE(ZBBH) and not UPDATE(YYBBH))------我的理解,此情况不会发生。 begin select @changekey=1 update t_sxktzt set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o update t_sxysmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o update t_sxssmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o update t_xw set zbbh=@ZBBH,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o end if UPDATE(YYBBH) begin select @changekey=1 update t_sxktzt set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o update t_sxysmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o update t_sxssmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o if (@YYBBH is not null) update t_xw set zbbh=@ZBBH,yybbh=@YYBBH,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o else update t_xw set zbbh=@ZBBH,yybbh=@YYBBH_o,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o end
if (UPDATE(ZBBH) and not UPDATE(YYBBH)) ------------------------------- 比如只更新ZBBH的时候就会执行到这样.
Create Or Replace Trigger TRIGGER_NAME After Insert Or Update Or Delete On TABLE_NAME For Each Row Begin --当操作类型为增加时 IF INSERTING THEN ...; --当操作类型为修改时 ELSIF UPDATING THEN ...; ELSIF DELETING THEN ...;--只有old值 END IF; END TRIGGER_NAME;
for insert
as
select * from inserted
GO
create trigger tu_表 on 表
for update
as
select * from inserted
select * from deleted
GO
create trigger td_表 on 表
for delete
as
select * from deleted
FOR UPDATE
AS
declare @count int,@c1 varchar(255)
declare @ZBBH char(5),@YYBBH int, @HTH char(10),@XZH char(6),@XXZH char(10)
declare @ZBBH_o char(5),@YYBBH_o int, @HTH_o char(10),@XZH_o char(6),@XXZH_o char(10)
declare @HTJE money,@HBLB char(6),@HTRQ datetime,@FHRQ datetime,@XDRQ datetime
declare @HTJE_o money,@HBLB_o char(6),@HTRQ_o datetime,@FHRQ_o datetime,@XDRQ_o datetime
declare @AZRQ datetime,@SJRQ datetime,@TXCJ char(20),@XZLB char(8)
declare @AZRQ_o datetime,@SJRQ_o datetime,@TXCJ_o char(20),@XZLB_o char(8)
declare @XZZT char(6),@XWS int,@JTHTH char(10),@XGRDM char(4)
declare @XZZT_o char(6),@XWS_o int,@JTHTH_o char(10)
declare @BPSL varchar(10),@BPBS int, @RCST varchar(20),@BUG varchar(20),@SBSJRQ datetime
declare @BPSL_o varchar(10),@BPBS_o int, @RCST_o varchar(20),@BUG_o varchar(20),@SBSJRQ_o datetime
declare @changekey int
select @count = @@rowcount
if (@count > 0)
BEGIN
select @changekey = 0
select @XGRDM = XGRDM from inserted
select @ZBBH = ZBBH from inserted
select @ZBBH_o = ZBBH from deleted
if (@ZBBH is not null) select @c1 = '总部编号='+'"'+rtrim(@ZBBH)+'"("'+rtrim(@ZBBH_o)+'")'select @YYBBH=YYBBH from inserted
select @YYBBH_o=YYBBH from deleted
if (@YYBBH is not null) select @c1 = @c1+',营业部编号="'+rtrim(@YYBBH)+'"("'+rtrim(@YYBBH_o)+'")'select @HTH=HTH from inserted
select @HTH_o=HTH from deleted
if (@HTH is not null) select @c1 = @c1+',合同号="'+rtrim(@HTH)+'"("'+rtrim(@HTH_o)+'")'
if (UPDATE(ZBBH) and not UPDATE(YYBBH))------我的理解,此情况不会发生。
begin
select @changekey=1
update t_sxktzt set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o
update t_sxysmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o
update t_sxssmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o
update t_xw set zbbh=@ZBBH,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o
end
if UPDATE(YYBBH)
begin
select @changekey=1
update t_sxktzt set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o
update t_sxysmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o
update t_sxssmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o
if (@YYBBH is not null)
update t_xw set zbbh=@ZBBH,yybbh=@YYBBH,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o
else
update t_xw set zbbh=@ZBBH,yybbh=@YYBBH_o,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o
end
FOR UPDATE
AS
declare @count int,@c1 varchar(255)
declare @ZBBH char(5),@YYBBH int, @HTH char(10),@XZH char(6),@XXZH char(10)
declare @ZBBH_o char(5),@YYBBH_o int, @HTH_o char(10),@XZH_o char(6),@XXZH_o char(10)
declare @HTJE money,@HBLB char(6),@HTRQ datetime,@FHRQ datetime,@XDRQ datetime
declare @HTJE_o money,@HBLB_o char(6),@HTRQ_o datetime,@FHRQ_o datetime,@XDRQ_o datetime
declare @AZRQ datetime,@SJRQ datetime,@TXCJ char(20),@XZLB char(8)
declare @AZRQ_o datetime,@SJRQ_o datetime,@TXCJ_o char(20),@XZLB_o char(8)
declare @XZZT char(6),@XWS int,@JTHTH char(10),@XGRDM char(4)
declare @XZZT_o char(6),@XWS_o int,@JTHTH_o char(10)
declare @BPSL varchar(10),@BPBS int, @RCST varchar(20),@BUG varchar(20),@SBSJRQ datetime
declare @BPSL_o varchar(10),@BPBS_o int, @RCST_o varchar(20),@BUG_o varchar(20),@SBSJRQ_o datetime
declare @changekey int
select @count = @@rowcount
if (@count > 0)
BEGIN
select @changekey = 0
select @XGRDM = XGRDM from inserted
select @ZBBH = ZBBH from inserted
select @ZBBH_o = ZBBH from deleted
if (@ZBBH is not null) select @c1 = '总部编号='+'"'+rtrim(@ZBBH)+'"("'+rtrim(@ZBBH_o)+'")'select @YYBBH=YYBBH from inserted
select @YYBBH_o=YYBBH from deleted
if (@YYBBH is not null) select @c1 = @c1+',营业部编号="'+rtrim(@YYBBH)+'"("'+rtrim(@YYBBH_o)+'")'select @HTH=HTH from inserted
select @HTH_o=HTH from deleted
if (@HTH is not null) select @c1 = @c1+',合同号="'+rtrim(@HTH)+'"("'+rtrim(@HTH_o)+'")'
if (UPDATE(ZBBH) and not UPDATE(YYBBH))------我的理解,此情况不会发生。
begin
select @changekey=1
update t_sxktzt set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o
update t_sxysmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o
update t_sxssmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o
update t_xw set zbbh=@ZBBH,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o
end
if UPDATE(YYBBH)
begin
select @changekey=1
update t_sxktzt set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o
update t_sxysmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o
update t_sxssmx set xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and HTH=@HTH_o
if (@YYBBH is not null)
update t_xw set zbbh=@ZBBH,yybbh=@YYBBH,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o
else
update t_xw set zbbh=@ZBBH,yybbh=@YYBBH_o,xgrdm=@XGRDM,xgrq=getdate() where zbbh=@ZBBH_o and yybbh=@YYBBH_o and hth=@HTH_o
end
-------------------------------
比如只更新ZBBH的时候就会执行到这样.
After Insert Or Update Or Delete On TABLE_NAME
For Each Row
Begin
--当操作类型为增加时
IF INSERTING THEN
...;
--当操作类型为修改时
ELSIF UPDATING THEN
...;
ELSIF DELETING THEN
...;--只有old值
END IF;
END TRIGGER_NAME;