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