触发器怎用条件啊?能像VBA哪样做吗?

K仔

在SQL上建了个临时表[T - SDN],想做个触发器来update 并 insert 另一个表[T - sample]的记录

黑字为原始记录,[T - SDN]临时表,用户更新后会清空.当[T - SDN]有数据插入时,会以[T - SDN]的(orderno)查找[T - sample]中(sorder = orderno)的所有记录,如果(department = QC ,active = 1)的话就update 并 insert 红色字的记录.如果(department <> QC ,active =1)的话就 update 并 insert 蓝色的记录.

因为没有学过SQL的语句,有点迷茫,请各位帮帮忙,我用Excel 做了个例子和效果~希望各位明白,谢谢[em01]

K仔

救命啊~怎麼沒人回啊?都不會~還是不可能做到啊???

K仔

现在只想到这样写,但是上面红字的部份说是列名错了...蓝色是要引用的地方...还有是这样写语法对不对啊?

CREATE TRIGGER [update_sample] ON dbo.[T - Samples DN Header]
FOR INSERT
AS
declare @order_no char(13)
declare @footage real
declare @order_count int
declare @r_sides real
declare @r_from char
set @order_no=(select orderno from INSERTED)
set @footage=(select footage from INSERTED)
set @r_sides =(select receive_sides from deleted)
set @r_from =(select receive_from from deleted)
set @order_count=(select count(sorder) as bb from [T - sample] where department='QC' and active='1' and sorder=@order_no)
if @order_count>'0'
begin
     UPDATE [T - sample] SET [active]='0',transfer_to='FG WAREHOUSE', finished_sides=@footage,lossing_sides=@footage-receive_sides,finished_time=getdate()  WHERE [active]='1' and sorder=@order_no and department='QC'
      INSERT INTO [T - sample] ([sorder],[department],[receive_time],[receive_from],[receive_sides],[start_time],[active]) VALUES (@order_no ,'FG WAREHOUSE',getdate(),'QC',@footage,getdate(),'1')
end
begin
     UPDATE [T - sample] SET [active]='0',transfer_to='QC', finished_sides=@r_sides,finished_time=getdate()  WHERE [active]='1' and sorder=@order_no and department <>'QC'
      INSERT INTO [T - sample] (.....,[receive_from],[receive_sides],...) VALUES (...,@r_from,@r_sides,...@footage-@r_sides,...)
       INSERT INTO [T - sample] ([sorder],[department],[receive_time],[receive_from],[receive_sides],[start_time],[active]) VALUES (@order_no ,'FG WAREHOUSE',getdate(),'QC',@footage,getdate(),'1')
end