Update trigger

Transact-SQL
1

my table has the following columns:

Product Code, Supplier, Hold, Qty

Example:

Prod1, AERO1, Y, 200
PROD1,AERO3, Y,300
PROD1,AERO5,Y,456
PROD1,AERO10,Y,67

ONLY if HOLD IS UPDATED TO "" THEN
NEED TO SELECT ALL RECORDS EQUAL TO ProductCode and update HOLD TO ""

OTHER UPDATES TO HOLD SHOULD BE IGNORED.

THANKS

2

Hi

I tried something
I dont know if it works
Have to test it

DROP Create Data 
drop table #data 

go 


CREATE TABLE #data 
  ( 
     ProductCode varchar(100) , 
     Supplier varchar(100),
     Hold varchar(100),
     Qty int
  ) 

go 

insert into #data select 'Prod1', 'AERO1', 'Y', 200
insert into #data select 'PROD1','AERO3', 'Y',300
insert into #data select 'PROD1','AERO5','Y',456
insert into #data select 'PROD1','AERO10','Y',67

go 


select * from #data
go
TRIGGER 
CREATE TRIGGER trg 
ON #data 
after UPDATE 
AS 
  BEGIN 
      SET nocount ON; 

      UPDATE #data 
      SET    hold = '' 
      FROM   #data t 
             INNER JOIN inserted i 
                     ON t.productcode = i.productcode 
                        AND i.hold = '' 
  END
3

This works however now I need to check if any new records added or updated.

For example I need to consider an extra field in this table. Called Note(varchar (30)

if any new lines are added or updated and HOLD is like 'Y'
Have to check if same ProductCode exists In the table with HOLD LIKE 'Y' as well.
If found (could be more than one) and if its Note is like 'QC' and Hold is like 'Y' THEN I need to update Note field of the inserted or updated record. to 'QC'

4

hi patyk

Looking at what you wrote

i am trying pseudo code which will be used for final SQL

if any new lines are added or updated and HOLD is like 'Y'

pseudo code 
my translation
case when inserted.count > 0 or  (any new lines are added) AND HOLD = 'Y'
should it be like this  
(inserted.count > 0) OR (any new lines are added) AND (HOLD = 'Y')  
OR like this 
( ((inserted.count > 0) OR (any new lines are added) ) AND (HOLD = 'Y')

Have to check if same ProductCode exists In the table with HOLD LIKE 'Y' as well.

pseudocode 
inserted a join table b on 
a.productcode = b.productcode and b.HOLD = 'Y'

If found (could be more than one) and if its Note is like 'QC' and Hold is like 'Y'
THEN I need to update Note field of the inserted or updated record. to 'QC'

pseudocode 
update  a set a.NotE = 'QC' 
from table A join inserted b 
where exists 
table.NOTE like 'QC' AND table.Hold = 'Y'