Delete trigger or before delete when record in sql 2005? 3

[DCSage]

Hello.

I want to record changes to a table when a user tries to delete a record. I initially thought that a Delete Trigger would be sufficient, but after searching online, I realize that a BEFORE DELETE statement may be better. I am using sql server 2005. how would the trigger be called from my c# code? or is the trigger raised automatically?

I created a table to store the deleted values each time it is removed from the main table. but when I run it to check my syntax, I have alot of errors. Is what I am proposing even possible?

Msg 102, Level 15, State 1, Procedure trg_InventoryDelete, Line 1
Incorrect syntax near 'BEFORE'.

TRIGGER

CREATE TRIGGER trg_InventoryDelete BEFORE DELETE
ON InventoryResults_Backup
FOR Each row

AS

Declare @userDeleting varchar(50)
Declare @deleteDate smalldatetime 

--insert record into audit table
BEGIN 

 
Insert INTO recycleBin (DeletedBy, DateDeleted, InventoryID, Bldg, equipLoc, fk_location, equipment, brand, roomno, floorno,dept,deptno, modelno,serialno,comments, dateInserted)
SELECT @userDeleting, @deleteDate, InventoryID, Bldg, equipLoc, fk_location, equipment, brand, roomno, floorno,dept,deptno, modelno,serialno,comments, dateInserted  FROM InventoryResults_Backup

RETURN

END

 

[RiverGuy]

It looks like you have Oracle or some other SQL syntax. SQL Server does not have BEFORE triggers. There are only AFTER and INSTEAD of triggers. There's also no "For each row."

CREATE TRIGGER trg_InventoryDelete 
ON InventoryResults_Backup
FOR INSERT, UPDATE, DELETE

AS


--insert record into audit table
BEGIN

 
Insert INTO recycleBin (OperationType, DeletedBy, DateDeleted, InventoryID, Bldg, equipLoc, fk_location, equipment, brand, roomno, floorno,dept,deptno, modelno,serialno,comments, dateInserted)
SELECT 'D', SUSER_SNAME(), GETDATE() , InventoryID, Bldg, equipLoc, fk_location, equipment, brand, roomno, floorno,dept,deptno, modelno,serialno,comments, dateInserted FROM DELETED

Insert INTO recycleBin (OperationType, DeletedBy, DateDeleted, InventoryID, Bldg, equipLoc, fk_location, equipment, brand, roomno, floorno,dept,deptno, modelno,serialno,comments, dateInserted)
SELECT 'I', SUSER_SNAME(), GETDATE() , InventoryID, Bldg, equipLoc, fk_location, equipment, brand, roomno, floorno,dept,deptno, modelno,serialno,comments, dateInserted FROM INSERTED


RETURN

END

Look at the above code. There are two virtual tables being used: INSERTED and DELETED. INSERTED will have your new values, and DELETED will contain your old values. If your user performs a delete, you will only have rows in your DELETED table. An insert will contain rows in your INSERTED table only. An update will have rows in both. The above trigger would log all operations. And it would be called automatically from C#.

 

[gmmastros]

It looks like you found some DB2 code to model your trigger on. The syntax for SQL2005 is a bit different. Specifically, there is no 'BEFORE', and there is no 'FOR EACH ROW'. Also, you wouldn't want to copy all the rows from the table in to the recycle bin. You probably only want rows that are in the process of getting deleted. I think the code you are looking for is....

[!]CREATE TRIGGER trg_InventoryDelete 
ON InventoryResults_Backup
FOR Delete[/!]

AS

--insert record into audit table
BEGIN

[!]SET NOCOUNT ON[/!]

Insert INTO recycleBin (DeletedBy, DateDeleted, InventoryID, Bldg, equipLoc, fk_location, equipment, brand, roomno, floorno,dept,deptno, modelno,serialno,comments, dateInserted)
SELECT suser_sname(), GetDate(), InventoryID, Bldg, equipLoc, fk_location, equipment, brand, roomno, floorno,dept,deptno, modelno,serialno,comments, dateInserted  
FROM [!]deleted[/!]

END

Notice the syntax for declaring the cursor. This trigger will fire automatically just before the data is actually removed from the table. Next, notice the table I select from. There are 2 pseudo tables available within a trigger. the Inserted table will only have data for inserts and updates. the Deleted table will only contain rows for deletes and updates. The structure of the Deleted and Inserted tables will match exactly the table that is getting changed (identified by the ON clause in the trigger definition, at the top).

You do not need to call this from your front end code. This code will get executed automatically when a row is deleted from the table.

Lastly, it's good practice to include 'SET NOCOUNT ON' in every trigger you write.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DCSage]

OMG. Thank you both. I searched for a deleted table, an I was given 'invalid option'. is there some setting i have to turn on? I am using sql 2005 express developer's edition.

 

[markros]

Inserted and Deleted are two special tables that are only visible outside the trigger and in the INSERT/UPDATE/DELETE/MERGE commands using special new OUTPUT clause.

Take a look

http://technet.microsoft.com/en-us/library/ms177564.aspx

PluralSight Learning Library

 

[DCSage]

Thank you all so much! I have been pouring through alot of documentation on Triggers and Cursors. Is there a particular resource you all could recommend?

 

[gmmastros]

Uh oh. You said cursors. Cursors are rarely needed, and are almost always slower than set based operations. Do yourself a favor, if you *think* you need to use a cursor for something, post a question here instead. There are many people that will gladly show you how your cursor based operation can be rewritten in a set based way.

I friend of mine just wrote this article about cursors. I strongly encourage you to read it.

http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DCSage]

You all are awesome!!! Thank you so much!

 

[SQLSister]

Thanks George for referencing my article.

"NOTHING is more important in a database than integrity." ESquared