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)

Trigger

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-06-18 : 13:01:51

Can u help check out the trigger for me

Only when the employee record is added inserted I want it then to go and do an update and set this PIN_UPDATED_FL to Y for the employee record that i am updating. I.e this be my employee record

CREATE TRIGGER TR_Empl_Ess_Flag
ON [DELTEK].[EMPL]
AFTER INSERT, UPDATE
AS

UPDATE X
SET PIN_UPDATED_FL = 'Y' ) FROM DELTEK.EMPL X

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

Does this seem ok

Kristen
Test

22859 Posts

Posted - 2007-06-18 : 13:37:04

"[i]Only when the employee record is added inserted I want it then to go and do an update and set this PIN_UPDATED_FL to Y for the employee record that i am updating"

So do you want to set PIN_UPDATED_FL when the record is Inserted, or when it is Updated, or Both??

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-06-18 : 14:13:35

When new record, basically set the FLAG to Y

When updating the record (do not reset flag keep as Y)

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 03:28:04

CREATE TRIGGER TR_Empl_Ess_Flag
ON [DELTEK].[EMPL]
AFTER INSERT, UPDATE
AS


UPDATE X
SET   PIN_UPDATED_FL = 'Y'
FROM  DELTEK.EMPL AS X
      JOIN inserted AS I
          ON I.EMPL_ID = X.EMPL_ID
      LEFT OUTER JOIN deleted AS D
          ON D.EMPL_ID = X.EMPL_ID
WHERE D.EMPL_ID IS NULL -- "Inserted" records only

Technically you don't need this to be an UPDATE trigger, however you may have other statements you want to add for Update trigger conditions, so I've written it to work in an UPDATE trigger (as well as an INSERT trigger)

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-06-19 : 06:21:54

Thanks i try that out.

There was one other trigger i changed to and it does not fire so i put back the old one and that is not working either, i cannot figure out why the old trigger did work and after deleting it and re adding it, it not work.

This one happens to be in production oh my. Anything i can check?

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 07:57:46

Has it been created "owned" by the wrong person?

Perhaps the original was owned by "dbo" and the replacement is owned by You ?

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-06-19 : 09:33:18

Did not think to check owner.

Thanks very much.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-06-19 : 09:35:36

One little question when you do the insert then go to do update
is the empl_id null here as its not written to the database?
Not understanding checking NULL?

UPDATE X
SET PIN_UPDATED_FL = 'Y'
FROM DELTEK.EMPL AS X
JOIN inserted AS I
ON I.EMPL_ID = X.EMPL_ID
LEFT OUTER JOIN deleted AS D
ON D.EMPL_ID = X.EMPL_ID
WHERE D.EMPL_ID IS NULL -- "Inserted" records only

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 10:36:23

"when you do the insert then go to do update is the empl_id null here as its not written to the database?"

No, the record is already in the database (unless you use an INSTEAD OF trigger), so you can "Update" a record in an "Insert" trigger, because it will already be in the database when the Trigger fires.

(You can also force a Rollback in a Trigger, which will in effect remove the just-inserted-record from the table)

I am checking NULL in the "deleted" pseudo table, using a LEFT OUTER JOIN, which is my way of checking that there is NO record in "deleted" - i.e. this is an Insert and not an Update.

You might be more comfortable with an EXISTS?


UPDATE X
SET   PIN_UPDATED_FL = 'Y'
FROM  DELTEK.EMPL AS X
      JOIN inserted AS I
          ON I.EMPL_ID = X.EMPL_ID
WHERE NOT EXISTS (SELECT * FROM deleted AS D WHERE D.EMPL_ID = X.EMPL_ID)

or even:


UPDATE X
SET   PIN_UPDATED_FL = 'Y'
FROM  DELTEK.EMPL AS X
WHERE X.EMPL_ID IN (SELECT EMPL_ID FROM inserted)
      AND NOT EXISTS (SELECT * FROM deleted AS D WHERE D.EMPL_ID = X.EMPL_ID)

they will all do the same job, choose the syntax you find easiest.

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-06-19 : 14:38:30

Thanks you i take a look over the code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources