Writing a trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Writing a trigger

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-27 : 13:34:01

First time writing a trigger

This needs to update reason code when new record inserted and
updated records.

Is this correct for both cases.

Getting confused on UPDATE x
as i usually do UPDATE (TABLENAME) here or
UPDATE DELTEK.EMPL_LAB_INFO X

Do i need to change below ?

CREATE TRIGGER TR_Empl_Lab_Info
ON DELTEK.EMPL_LAB_INFO
AFTER UPDATE, INSERT
AS
UPDATE x
SET PERS_ACT_RSN_CD = 'NCT'
FROM DELTEK.EMPL_LAB_INFO X
INNER JOIN INSERTED on x.EMPL_ID = inserted.EMPL_ID and
x.EFFECT_DT = inserted.EFFECT_DT
WHERE x.REASON_DESC = 'NEW HIRE CONTRACT TRANSITION'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 13:37:32

What is DELTEK? table owner?

Harsh Athalye
India.
"Nothing is Impossible"

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-27 : 13:41:21

Yes DELTEK is the database owner

Kristen
Test

22859 Posts

Posted - 2006-11-27 : 15:05:06

probably not relevant, but on a case-sensitive database the use of "INSERTED" and "inserted" isn't going to work, and that apart its sloppy - no offence intended.

Alias it if you prefer:


INNER JOIN inserted AS I
     ON x.EMPL_ID = I.EMPL_ID 
    AND x.EFFECT_DT = I.EFFECT_DT

So your requirement is:

If a record is INSERTED or UPDATE and the REASON_DESC is set to 'NEW HIRE CONTRACT TRANSITION' then overwrite the PERS_ACT_RSN_CD with 'NCT'? If so you've got it!

I would have added to the WHERE:

AND COALESCE(PERS_ACT_RSN_CD, '') <> 'NCT'

to avoid re-updating records that already have that field set.

I also presume the PK is EMPL_ID, EFFECT_DT (i.e. given values for those two fields are unique)

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-27 : 15:32:54

Thanks for your guidance.
x (is the empl lab info record im on)
i (is the inserted record that is being inserted)

So final trigger is as follows:

CREATE TRIGGER TR_Empl_Lab_Info
ON DELTEK.EMPL_LAB_INFO
AFTER UPDATE, INSERT
AS
UPDATE x
SET PERS_ACT_RSN_CD = 'NCT'
FROM DELTEK.EMPL_LAB_INFO X

INNER JOIN inserted AS I
ON x.EMPL_ID = I.EMPL_ID
AND x.EFFECT_DT = I.EFFECT_DT

WHERE x.REASON_DESC = 'NEW HIRE CONTRACT TRANSITION'
AND COALESCE(PERS_ACT_RSN_CD, '') <> 'NCT'

Kristen
Test

22859 Posts

Posted - 2006-11-27 : 15:46:50

I reckon that's going to do the trick, but I'll leave the careful, pedantic, testing down to you!

Although I'm a bit bothered that you asked in the first place: Your original code looked fine to me, was there something praying on your mind about it? Perhaps there is some nuance that might be troublesome if you speak-out a little more?

But I may just be being pedantic, as defensive-programming is my style - if so just ignore me!

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-28 : 08:42:01

Thanks i tested it seemed to work...both in SQL and in the erp application...

Asked question cause it the first trigger i have ever written and like to just check to make sure ...and i could not understand

UPDATE x ...as im so used to working with
UPDATE MYTABLENAME and not using x

I understand the I part.

Kristen
Test

22859 Posts

Posted - 2006-11-28 : 09:33:50

I always use the "AS" keyword, as I think it makes the SQL more readable:

...
FROM DELTEK.EMPL_LAB_INFO AS X
...

don't know if that helps at all though!

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-28 : 09:39:32

Not for nothing....but

I don't get it...this could probbaly be done with a constraint instead

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-28 : 10:55:06

but in a trigger

CREATE TRIGGER TR_Empl_Lab_Info
ON DELTEK.EMPL_LAB_INFO
AFTER UPDATE, INSERT
AS
UPDATE x

u do not need to do

CREATE TRIGGER TR_Empl_Lab_Info
ON DELTEK.EMPL_LAB_INFO
AFTER UPDATE, INSERT
AS
UPDATE DELTEK.EMPL_LAB_INFO as X

Thats the bit that got me confused...i was i know im on the table but im so used to put table name in there.

Kristen
Test

22859 Posts

Posted - 2006-11-28 : 11:37:32

You can't actually put the Alias there, it has to be after the FROM (AFAIK)

You can do


UPDATE MyTable
SET MyColumn = 'FOO'
WHERE MyColumn = 'BAR'

UPDATE MyTable
SET MyColumn = 'FOO'
FROM MyTable
WHERE MyColumn = 'BAR'

which is overkill of course, but it leads to the syntax required when you have a JOIN:


UPDATE MyTable
SET MyColumn = 'FOO'
FROM MyTable
    JOIN MyTable2
        ON MyTable2.ID = MyTable.ID
WHERE MyColumn = 'BAR'

but I prefer to use aliases at that point, and in fact I always alias the table-to-be-updated as U (so that whatever UPDATE source code I am looking at I am less likely to mistake the table that is being updated:


UPDATE U
SET MyColumn = 'FOO'
FROM MyTable AS U
    JOIN MyTable2 AS T2
        ON T2.ID = U.ID
WHERE MyColumn = 'BAR'

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-28 : 12:18:45

Never seen it written like that at the top of the UPDATE..

Good to learn.

Thanks for your guidance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources