Trigger to envoke Stored Procedure

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 to envoke Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-08 : 08:01:25

Mike writes "Hi, I'm having a lot of problems with my SQL Server 8.0. I'm trying to create a trigger that will call a stored procedure when an update, insert or delete occurs. This procedure will basically sweep through the database and clean out bad data.

First, here's my stored 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);

And here is my Trigger:
=======================
Create Trigger CleanUp on Location
for insert, update, delete
as
Exec CleanData
GO

When I just run CleanData, it works perfectly fine. However, when the Location table is altered and trigger calls the procedure, I get this error message:

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

Do you see what I'm doing wrong?

Thanks a lot!
Mike"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-08 : 12:19:57

Are you sure that's your trigger code? Generate both from Enterprise Manager using the generate sql script wizard. Post the results here. Are you running service pack 3a?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-08 : 12:25:44

Never mind that...you want your queries to take forever?

Why don't you just establish foreign key constraints?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources