whats wrong with my trigger ? - 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 2005 Forums

Transact-SQL (2005)

whats wrong with my trigger ?

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-01-15 : 12:31:48

Sql 2000:
I have a table, lets call it A and i want to define a trigger on it that will delete a record from another table based on the id of the deleted record in table A.

Something like this:
CREATE TRIGGER dbo.trgDelUserStuff ON dbo.tiTableA
FOR DELETE
AS

DELETE FROM tiUserStuff
WHERE tiUserStuff.user_stuff_id = deleted.id

The error i get is something like:
"the column prefix 'deleted' does not match with a table name or alias name used in the query"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-15 : 13:01:42

[code]
DELETE
FROM tiUserStuff
WHERE EXISTS (SELECT * FROM deleted WHERE tiUserStuff.user_stuff_id = id )
[/code]

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-16 : 17:41:43

Here's another way of doing it using a JOIN statement:

DELETE A
FROM tiUserStuff A INNER JOIN delete B
ON A.user_stuff_id = B.id

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources