Trigger for entire database 1

[MikeAJ]

Is it possible to set a trigger for the entire database that will execute a stored procedure when any table has an insert, update, or delete? I have a pretty big database, and I want to run a stored procedure to clean up the data after any table is modified in any way. I'm working with MSSQL 8.0.

Thanks,
Mike

 

[nigelrivett]

Nope they are table specific.
You can add the same trigger to every table though - have a look at

http://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MikeAJ]

Ok, when I put the trigger on the location table with this:

Create Trigger CleanUp on Location
for insert, update, delete
as
Exec CleanData
GO

I get an error saying I tried to pass parameter, but the stored procedure CleanData has none.

How do I correct this?

 

[nigelrivett]

What is the exact error message.
Could be from something in the SP or maybe some invalid characters in the script.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MikeAJ]

[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure CleanData has no parameters and arguments were supplied.

If I just run CleanData it works fine, but when a record is added to Location, this is the message that I get.

 

[MikeAJ]

and here in my procedure:

CREATE PROCEDURE CleanData AS
Delete From SubCategory Where CategoryID Not In(Select CategoryID From Category);
Delete From City Where StateID Not In(Select StateID From State);
Delete From Location Where CityID Not In(Select CityID From City);
Delete From BelongsTo Where SubCategoryID Not In(Select SubCategoryID From SubCategory);
Delete From BelongsTo Where LocationID Not In(Select LocationID From Location);
Delete From Amenity Where SubCategoryID Not In(Select SubCategoryID From SubCategory);
Delete From Feature Where AmenityID Not In(Select AmenityID From Amenity);
Delete From Feature Where LocationID Not In(Select LocationID From Location);

 

[MikeAJ]

So any idea why this is happening?

Thanks,
Mike

 

[SQLSister]

You could try removing the Go line.

 

[dash1b]

I hesitate to judge not knowing the app but does anyone else think this method of cleanup might be somewhat taxing to the server?

Trigger fires an SP with multiple DELETE's for every insert, update, or delete on any table?

I'm guessing this is not an OLTP app?
Might a timed SQL Agent job be better? Just my 1.5 cents.

 

[MikeAJ]

OK, that seems like it fixed that problem, but now this error message comes up:

[Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

 

[MikeAJ]

OK, I figured out why that is happening. I dropped that trigger and stored procedure and just decided to make a separate trigger for each table and only run the appropriate Delete statements.

Thanks for all the advice.