Update trigger does not allow me to update the table... 2

[childrenfirst]

Hi,

I created a trigger which will launch a stored procedure whenever an existing record is edited. The trigger looks like this:

CREATE TRIGGER [tr_EditTimeOff] ON [dbo].[TimeOff]
FOR UPDATE
AS

DECLARE @EmpName AS varchar(50)
DECLARE @Manager AS varchar(50)
DECLARE @RequestID AS int

SET @EmpName = (SELECT EmpName FROM UPDATED)

SET @Manager = (SELECT Manager FROM UPDATED)


EXEC s_TimeOffAction @EmpName,@Manager


The problem is that I got an error message every time I tried to update the table since I added this trigger. The error message says:

Invalida object name 'Updated'

Can anyone tell me why I got the error message and what I should revise the trigger in order to allow update to the existing records on the table?

Thank you in advance!!

 

[jbenson001]

There is no Update psuedo table, there is only Inserted and Deleted. In your case, use Inserted.

Jim

 

[childrenfirst]

Hello Jim,

Thank you very much for your suggestion. In fact, I created two triggers for the table. One is Inserted, and the other is Updated because I thought that updating a record could not trigger the Inserted trigger. I need something that will trigger the trigger and launch the stored procedure whenever a user add a record to the table or update/edit an existing record on the table. Do I need just the Inserted trigger?

Thank you very much,
Cathy

 

[bborissov]

You could create a one trigger FOR INSERT, UPDATE, that trigger will be fired after INSERT or UPDATE statement. But keep in mind that ALL triggers are fired AFTER the batch is completed. I mean that you can have many records in INSERTED or DELETED tables. Also as Cathy said there is no UPDATED table, you have only INSERTED and DELETED. That is because UPDATE statemetd first dele all records that match the WHERE clause and after that INSERT new ones.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

to be clear:

In an Update trigger, there is no Updated meta-table.

In all triggers, update, delete, or insert, there are two meta-tables available to you: Inserted and Deleted. Here's a grid to show how these tables work:

[tt] Table
Operation Inserted Deleted
Insert * empty
Update * *
Delete empty *[/tt]

So in an update, if you want to use the values that were present before the update, you would join to the Deleted table. If you want to use the values that are present after the update, use the Inserted table.

If you want an operation that functions during insert or update against the modified/inserted values, then make your trigger FOR INSERT, UPDATE and simply join to the Inserted table and you'll be good.

In some cases you can write some clever queries that join to both the inserted and deleted tables to handle all three modification operations at once. But this can be complicated.

 

[childrenfirst]

Thank you for the detailed explanation

 

[childrenfirst]

Hi guys,

Sorry for bugging you on the same issue again. I tried ESquared's suggestion and updated my Insert trigger to:

CREATE TRIGGER [tr_InsertTimeOff] ON [dbo].[TimeOff]
FOR INSERT, UPDATE
AS

DECLARE @EmpName AS varchar(50)
DECLARE @Manager AS varchar(50)
DECLARE @RequestID AS int
DECLARE @PreviousCount AS int

SET @EmpName = (SELECT EmpName FROM UPDATED INNER JOIN INSERTED ON UPDATED.RequestID = INSERTED.RequestID)

SET @Manager = (SELECT Manager FROM UPDATED INNER JOIN INSERTED ON UPDATED.RequestID = INSERTED.RequestID)

SET @PreviousCount = (SELECT MAX(PreviousCount) FROM UPDATED INNER JOIN INSERTED ON UPDATED.RequestID = INSERTED.RequestID WHERE EmpName = @EmpName)


EXEC s_TimeOffAction @EmpName,@Manager, @PreviousCount


The revision did not work... I think that I made some mistake in the JOIN (how should I join to the Inserted table if there is no updated table?). Could you help me work out this issue so that the INSERT trigger can function against the inserted or modified values?

Thank you very very much!

 

[SQLSister]

there is no updated table you must remove any reference to it. There is no need whatsoever to joinj to an updated table.

Further your trigger will not work if more than one record is inserted or updated so the whole structure needs to change. (I'm just a ray of joy tonight, huh?) it is a very bad practice to ever write a trigger that assumes only one record will be updated even if you think that will be true. Sooner or later it will not and you will create a major data integrity problem.

first thing you need to do is take the code in the sp you are calling and convert it to act on a set of data vice one record at a time. This code will then need to go in your trigger rather than the refernce to the sp. Alternaively you coudl run this through a cursor but you won;t like it if you ever have an insert or update that does more than a few records (and believe me, someday you will). Better to write this correctly the first time than have a production system blow up on you. Trust me you do not want to try to fix a trigger induced data integrity problem.

we could help you better if we understood what you really want to do with this trigger.

Questions about posting. See faq183-874

 

[ESquared]

SQLSister is 100% right, I was just feeling too lazy to try to explain it all.