April 19, 2006 at 9:04 am
Hi Experts:
I need to create a trigger on a given table for delete
To track who is deleting records on this table.
the trigger is as follows:
CreateTRIGGER Trig_Delete_Search
ON Search
FOR DELETE
AS
Insert DeletionHistory
Select Firmfile,SYSTEM_USER,Getdate() From SEARCH
GO
Their is a logical error on the select statement..
I need to insert only the most recent file that is deleted how do I do that. At present it selects all the files in the search table ...
Please advise!!
April 19, 2006 at 9:18 am
Look up 'deleted tables' in BOL. You need to select firmfile, system_user(), getdate() from DELETED.
April 19, 2006 at 9:55 am
Ok I update the select statement as follows:
Insert DeletionHistory
Select deleted.Firmfile,SYSTEM_USER,Getdate() From SEARCH,deleted
WHERE SEARCH.FirmFile = deleted.FirmFile
However nothing is recorded??
April 19, 2006 at 9:55 am
right - just to clarify - sql server creates a table called "Deleted" that temporarily stores the deleted row(s)...so that's the one one you need to select from...
**ASCII stupid question, get a stupid ANSI !!!**
April 19, 2006 at 9:59 am
never mind thanks Sushila I see what you are saying..
April 19, 2006 at 11:44 pm
Hi,
You try this.
create trigger trig_delete_search
on search for delete
as
insert into deletionhistory
(select firmfile,system_user,getdate() from
deleted);
end
Ramaa
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply