Need help with Trigger

[leprikon]

I'm trying to modify a field on the record as it comes in if it meets certain criteria. What am i doing wrong ?

CREATE TRIGGER Shipping_Instructions ON dbo.table1
FOR UPDATE
AS
BEGIN
update dbo.table1
set shdel2 = 'Special Requirment'
where shan8 IN ('202010','202012','202167','202564','202632','202711','202743')

 

[dky1e]

You want an "instead of trigger".

And you will need:
(SELECT trigger_nestlevel( object_ID('Shipping_Instructions') ) ) = 1

 

[leprikon]

what is the following doing ?

(SELECT trigger_nestlevel( object_ID('Shipping_Instructions') ) ) = 1

 

[dky1e]

It makes sure that the code will be executed only once, ie if you perform an update from another trigger.

Check out Books Online, read it carefully because triggers can be "hazardous" if not implemented correctly.

Another thing, everytime you do an update the entire table (all rows) will be scanned and updated... i haven't read the entire post before. What exactly are you trying to accoplish?

 

[leprikon]

as each record is written to table1. If the value of field shan8 is equal to '202010','202012','202167',or '202564'
then set the value of field shdel2 to 'Special Requirement' on the inserted record.

Basically we have our Cust Department not entering notes on each record, which is needed by our Shipping Dept for these specific customer numbers.

 

[alfredp77]

the other way to avoid the recursion call (other than dky1e already mentioned) is like this:

if update(SHDEL2)
return

and to make sure you only update one row of data each time
(only the updated data, not the entire table), you should do a join with the "inserted" table.

update dbo.table1
set shdel2 = 'Special Requirment'
from dbo.table1 t join inserted i
on (t.XXX = i.XXX)
where t.shan8 IN ('202010','202012','202167','202564','202632','202711','202743')

where XXX is a column which is the unique key of the inserted data (actually can be 2 or more columns).

just curious, why would you use an UPDATE trigger if you want to change the description when the data is INSERTED?

alfredp77

 

[leprikon]

should be on insert then ?