1.mssql触发器的写法，一个demo
放个demo供以后模仿：
//新增时触发
CREATE TRIGGER [In_EgStock] ON [dbo].[est_outlist]
after insert
AS
declare @New_Weight float
select @New_Weight=weight from inserted
UPDATE P SET
p.isSaleLock = 1 , p.salelockweight = isnull(p.salelockweight,0) +@New_Weight
FROM eg_stock P,est_outlist G where G.stockid=p.stockid
//更新时触发
CREATE TRIGGER [Up_EgStock] ON [dbo].[est_outlist]
after UPDATE
AS
declare @Old_weight float
declare @New_Weight float
declare @isdel int
select @isdel=isdel from inserted
select @Old_weight=weight from deleted
select @New_Weight=weight from inserted
if update(weight)
begin
UPDATE P SET p.isSaleLock = 1 , p.salelockweight = isnull(p.salelockweight,0) + (@New_Weight- @Old_weight) FROM eg_stock P,est_outlist G where G.stockid=p.stockid and G.isdel<>1
UPDATE P SET p.isSaleLock = 0 FROM eg_stock P,est_outlist G where G.stockid=p.stockid and p.salelockweight=0
end
else
if update(isdel)
begin
if @isdel=1
begin
UPDATE P SET p.salelockweight = isnull(p.salelockweight,0) - @New_Weight FROM eg_stock P,est_outlist G where G.stockid=p.stockid
end
else
if @isdel=0
begin
UPDATE P SET p.salelockweight = isnull(p.salelockweight,0) +@New_Weight FROM eg_stock P,est_outlist G where G.stockid=p.stockid
end
end
2.触发器 如何判断是 插入触发的，还是更新触发的？
if exists (select 1 from inserted) and not exists (select 1 from deleted)
--insert
if exists (select 1 from inserted) and exists (select 1 from deleted)
--update
if not exists (select 1 from inserted) and exists (select 1 from deleted)
--delete
区别：
插入触发:inserted 有值，deleted为null
更新触发：inserted 、deleted都有值
删除触发：inserted为null，deleted都有值
3.触发器的一些参考代码
CREATE TRIGGER tg_Insert_Prc ON [dbo].[SYS_INST_PRCS]
FOR Insert
AS
Declare @taskid int,@grouptype nvarchar(30),@prcid int
Declare @procuser nvarchar(30), @lduser nvarchar(30),@memuser nvarchar(30)
select @prcid = prc_id,@taskid=prc_taskid,@procuser = ltrim(rtrim(prc_procuser)) from inserted
if @procuser <>''
begin
select @grouptype = grouptype from Ghy_TaskID where taskid = @taskid
select @memuser = memuser from Ghy_ChUser where lduser = ltrim(rtrim(@procuser))
if @memuser <> ''
begin
select @lduser=lduser from Ghy_ChUser where memuser = @memuser and grouptype = @grouptype
update sys_inst_prcs set prc_procuser = @lduser where prc_id = @prcid
end
end
——————————————————————————————————————————
CREATE TRIGGER tg_getflowuser ON [dbo].[Test01]
FOR Insert
AS
declare @taskid int
select @taskid = taskid from inserted
update test01 set flowto='zhangdan' where taskid=@taskid
CREATE TRIGGER tg_getflowuser ON [dbo].[Test01]
FOR UPDATE
AS
declare @taskid int
select @taskid = taskid from inserted
update test01 set flowto='liyun' where taskid=@taskid
CREATE TRIGGER tg_getflowuser ON [dbo].[Test01]
FOR Insert
AS
declare @postman nvarchar(30),@GroupName_post nvarchar(30),@GroupName nvarchar(30)
declare @GPLD_USer nvarchar(30)
declare @taskid int
declare @copgroupname nvarchar(30)
set @copgroupname = '加工生产部'
select @taskid = taskid ,@postman = postman from inserted
select @GroupName_Post = gpmem_group from SYS_BPS_GPMEMLNK where GPMEM_User= @postman and GPMEM_IsPrimTerm = 1
if @GroupName_Post = ''
begin
select @GroupName_Post = gpld_group from sys_bps_gpldlnk where GPld_User= @postman and GPLD_IsPrimTitle = 1
end
if @GroupName_Post <> ''
begin
Declare cus_gp CURSOR
for select gpld_group,GPLD_USer from sys_bps_gpldlnk where gpld_group in (select gpmem_group from SYS_BPS_GPMEMLNK where GPMEM_User= @postman) order by GPLD_IsPrimTitle desc
open cus_gp
FETCH cus_gp Into @GroupName,@GPLD_USer
while @@fetch_status = 0
if dbo.F_IsInGp(@GroupName,@copgroupname) = 1 and dbo.F_IsInGp(@GroupName_Post,@copgroupname) = 1
begin
update test01 set flowto= @GPLD_USer where taskid= @taskid
end
CLOSE cus_gp
DEALLOCATE cus_gp
end
-------
CREATE TRIGGER tg_updateflowuser ON [dbo].[Test01]
FOR Insert
AS
declare @flowto nvarchar(30),@GroupName_post nvarchar(30),@GroupName nvarchar(30)
declare @GPLD_USer nvarchar(30)
declare @taskid int
declare @copgroupname nvarchar(30)
set @copgroupname = '加工生产部'
select @taskid = taskid ,@flowto = flowto from inserted
select @GroupName_Post = gpmem_group from SYS_BPS_GPMEMLNK where GPMEM_User= @flowto and GPMEM_IsPrimTerm = 1
if @GroupName_Post = ''
begin
select @GroupName_Post = gpld_group from sys_bps_gpldlnk where GPld_User= @flowto and GPLD_IsPrimTitle = 1
end
if @GroupName_Post <> ''
begin
Declare cus_gp CURSOR
for select gpld_group,GPLD_User from sys_bps_gpldlnk where gpld_group in (select gpmem_group from SYS_BPS_GPMEMLNK where GPMEM_User= @flowto) order by GPLD_IsPrimTitle desc
Declare cus_gp2 CURSOR
for select gpld_group,GPLD_User from sys_bps_gpldlnk where gpld_group in (select group_parent from sys_bps_groups where group_name in (select gpld_group from sys_bps_gpldlnk where GPld_User= @flowto)) order by GPLD_IsPrimTitle desc
open cus_gp
FETCH cus_gp Into @GroupName,@GPLD_User
begin
if @@fetch_status = 0
while @@fetch_status = 0
begin
if dbo.F_IsInGp(@GroupName,@copgroupname) = 1 and dbo.F_IsInGp(@GroupName_Post,@copgroupname) = 1
begin
update test01 set flowto= @GPLD_USer where taskid=@taskid
goto tg_label
end
FETCH cus_gp Into @GroupName,@GPLD_User
end
else
open cus_gp2
FETCH cus_gp2 Into @GroupName,@GPLD_User
if @@fetch_status = 0
while @@fetch_status = 0
begin
if dbo.F_IsInGp(@GroupName,@copgroupname) = 1 and dbo.F_IsInGp(@GroupName_Post,@copgroupname) = 1
begin
update test01 set flowto= @GPLD_USer where taskid=@taskid
goto tg_label2
end
FETCH cus_gp2 Into @GroupName,@GPLD_User
end
else
update test01 set flowto= null where taskid=@taskid
end
tg_label:
CLOSE cus_gp
DEALLOCATE cus_gp
tg_label2:
CLOSE cus_gp2
DEALLOCATE cus_gp2
end
else
update test01 set flowto = null where taskid=@taskid
——————————————————————————————————————————
评论