Trigger for update - 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

Transact-SQL (2000)

Trigger for update

Author

Topic

nimmie82
Starting Member

4 Posts

Posted - 2005-07-19 : 03:47:15

I have written a trigger. it has to be fired while update. But i'm not getting any response. Please check the commands...

CREATE trigger trigger_eff
on proj_progress
for update
as
declare @empid varchar(25),@status int,@approve char(10),@elementid varchar(25)
declare @startdt datetime,@deadline datetime

select @empid=(select emp_id from inserted)
select @status =(select status from inserted)
select @approve =(select approve from inserted)
select @elementid =(select approve from inserted)
if @status=100 and @approve='no'
begin
"perform all action"...
end

Thast my trigger. when i fire a update statemetn to the table progress, i'm not getting a record in the efficiency table. I don't know the reason.

Like in the insert trigger we can retrieve the values from the inserted record ...like select from inserted. How to get taht from a updated record. Please provide me with the solution.

Kristen
Test

22859 Posts

Posted - 2005-07-19 : 07:58:01

For an updated record there will be a row in "inserted" and another row in "deleted", so I don't think that is your problem.

However, what happens if you update more than one row? "select status from inserted" will give you the status from one of the rows - no telling which row, do I don't think your approach will work very well.

Can you do something like


INSERT INTO SomeAuditTable
SELECT emp_id, status, approve, ...
FROM inserted I
WHERE I.status = 100 and I.approve = 'no'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources