TRIGGERS - I feel shot away ! 2

[ibearian]

I AM QUITE ACCOMPAISHED AS SOME THINGS, I AM HOWEVER SUFFERING AT TRIGGERS, & THIS PARTICULAR ONE.

I HAVE TO UPDATE A FIELD IN THE SAME db AS THE FIELD CAUSING THE TRIGGER TO FIRE, but this field is not being inserted, it is being updated.



With my other triggers are at the point of the record being inserted and i can use the


CREATE TRIGGER TEST_UPDATE

SELECT MAX(tpid) AS r FROM table

IF UPDATE(NCATEGORYCODE3)

UPDATE NL_ACCOUNTS SET NCATEGORYCODE2 =(93)
WHERE tpid = R

methodology and it works ok

but what i can't seem to find out is how to referance a row that is being edited I was looking for a fetchcurrentrow(tpid) function so i could use the sale update ... where logic as above


any ideas


many thanks



rory

 

[SQLDenis]

The inserted and deleted pseudo tables will have the before and after values

Are you using an update trigger?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ibearian]

i am not sure .... am I ?

this is what i imagine it should look like

CREATE TRIGGER test_update1
ON nl_accounts
AFTER UPDATE
AS
IF UPDATE(NCATEGORYCODE3)

UPDATE NL_ACCOUNTS SET NCATEGORYCODE2 =(93)
WHERE fetchcurrentrow(tpid) = tpid


*/ where fetchcurrentrow() is my imagined function that returns a field from the row where the trigger was fired from /*


does this make any sense

sorry for being such a muppet

 

[SQLDenis]

This should also take care of multi-row statements

CREATE  TRIGGER test_update1
ON nl_accounts
AFTER UPDATE
AS
IF @@ROWCOUNT =0
RETURN

IF UPDATE(NCATEGORYCODE3)
BEGIN
UPDATE n  SET NCATEGORYCODE2 =(93)
FROM NL_ACCOUNTS n 
JOIN inserted i ON n.tpid =i.tpid
END

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ibearian]

many thanks, spot on

do you have any recomended reading on triggers, thay are so powerfull but i can't seems to find much thats usefull

 

[SQLDenis]

Books on Line has a whole section on triggers or grab a copy of The Guru's Guide to Transact-SQL
by Ken Henderson

He covers it in 4 pages but that is like 20 pages in any other book
Go to the book store and read pages 330- 334

In my opinion this and Inside SQL server 2000 are the 2 books that every SQL server developer should buy


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ibearian]

THANKS


RORY