Trigger on Delete - 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 on Delete

Author

Topic

runprn
Starting Member

2 Posts

Posted - 2004-04-06 : 14:25:29

Hi - I'm one of those developers who also has to play the part of SQL Server DBA. Anyway, based on one of the articles on sqlteam.com, I am trying to create a trigger to grab the contents of a deleted row and write it into a separate table. The issue is that users sometimes accidentally delete these records, and I have to then restore them. (If I get a trigger working on this table, I'll need to create several others like it.)

I have the core syntax down, but I don't know how to populate the variables with the results of the select query on the 'delete' table. Here's what I have so far:

CREATE TRIGGER tr_teams_DELETE
ON teams
AFTER DELETE
AS

IF EXISTS (SELECT * FROM deleted)
BEGIN
DECLARE @teamID int
DECLARE @teamName nvarchar
DECLARE @teamDescription ntext
DECLARE @teamCreateDate smalldatetime
DECLARE @teamTypeID int
DECLARE @teamOwner int
DECLARE @teamStatus nvarchar
DECLARE @teamLastUpdated smalldatetime
DECLARE @teamUpdatedBy int

INSERT INTO deletedTeams (
teamID_del,
teamName,
teamDescription,
teamCreateDate,
teamTypeID,
teamOwner,
teamStatus,
teamLastUpdated,
teamUpdatedBy
)
VALUES (

)
END
GO

To sum up, how do I populate the VALUES for this insert query?

I appreciate any help or suggestions.
Paul

GunterKDR
Starting Member

5 Posts

Posted - 2004-04-06 : 14:47:55

Try using a select from the logical table, rather than variables.

Something akin to:

INSERT INTO deletedTeams
Select colA, colB, colN...
FROM Deleted

Good Morning-
Gunter

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. -- Benjamin Franklin

runprn
Starting Member

2 Posts

Posted - 2004-04-06 : 15:19:33

Thanks, Gunter.

So, this is what I came up with:

CREATE TRIGGER tr_teams_DELETE
ON teams
AFTER DELETE
AS

IF EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO deletedTeams
SELECT teamID, teamName, teamDescription, teamCreateDate, teamTypeID, teamOwner, teamStatus, teamLastUpdated, teamUpdatedBy, rowguid
FROM deleted
END
GO

However, I get the following error when checking syntax:

Error 311: Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

I modified the query slightly to test it out using query analyzer, and it worked fine. (e.g., "FROM teams WHERE teamID < 5") So, now I'm stumped. This article says to use INSTEAD OF triggers: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20815377.html

But, have no idea how...

Thanks,
Paul

BTW: Great signature quote. I have that same quote on my email signature line.

GunterKDR
Starting Member

5 Posts

Posted - 2004-04-06 : 15:27:50

You'll need to use the "instead of" trigger.
Pull the primary key of the intended victim.
Do something akin to:

INSERT INTO deletedTeams
SELECT colA, colB, ...
FROM Teams AS T INNER JOIN deleted AS D ON
T.primaryKeyCol = D.primaryKeyCol

Then kill 'em.

Good Morning-
Gunter

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. -- Benjamin Franklin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources