Using Trigger
-
I am trying to use SQL2000 trigger as in the following
CREATE TRIGGER Data_Changed
ON tblTest
FOR INSERT, UPDATE
AS
EXEC usp_Test dbTest
Go
When I try to create the trigger I get the following error from SQL2000
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'usp_Test'. The stored procedure will still be created.
Even though the stored procedure, the table tblTest are in the same database (i.e. dbTest)
What is wrong?
SHB
-
saad - it appears that you are trying to insert rows into a system table - sysdepends ?!?!
if that's the case:
1) you need to have 'allow updates' set to 1.
2) system tables can be updated using only system stored procedures.
3) last but not least...
"Caution Updating fields in system tables can prevent an instance of Microsoft® SQL Server™ from running or can cause data loss. If you create stored procedures while the allow updates option is enabled, those stored procedures always have the ability to update system tables even after you disable allow updates. On production systems, you should not enable allow updates except under the direction of Microsoft Product Support Services."....BOL
**ASCII stupid question, get a stupid ANSI !!!** -
Thank for the reply
No I was not inserting any rows. The error I found out was caused by unknown stored procedure usp_Test was generated by an onwer other than
dbo which what is the default. once I recreated the stored procedure with dbo as the owner then all is OK.
So for future reference I learned to fully qualify the sp from within a trigger
CREATE TRIGGER DATA_CHANGED
ON tblTEST
FOR INSERT, UPDATE
AS
-
You should always qualify all objects... this is just one of the problems you'll meet
.Why are you executing a sp instead of using the insert/deleted tables of the trigger?
-
Are you sure you read the code
.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply