January 21, 2011 at 7:37 am
Im trying to create a trigger that inserts values into an audit table, but also delete related data from another table, but I just cant seem to get it to work. Heres what I have so far
CREATE TRIGGER trg_ArtistGenre_on_delete_cascade ON ArtistGenre FOR DELETE
AS
IF @@rowcount = 0 RETURN
INSERT INTO ArtistGenreAudit SELECT GetDate(),ArtistGenreID,ArtistID,Genre FROM DELETED
GO
DELETE FROM SecondaryCategory WHERE SecondaryCategory.[ArtistID] = DELETED.[ArtistID]) AND (SecondaryCategory.[ArtistGenreID] = DELETED.[ArtistGenreID])
GO
but im getting these errors
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DELETED.ArtistID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DELETED.ArtistGenreID" could not be bound.
anyone help ?
January 21, 2011 at 7:44 am
DELETE SC FROM SecondaryCategory SC INNER JOIN DELETED D ON SC.[ArtistID] = D.[ArtistID] AND SC.[ArtistGenreID] = D.[ArtistGenreID]
January 21, 2011 at 7:44 am
BTW you can't have a go statement in the definition of the trigger... the code will stop there.
January 21, 2011 at 7:46 am
here si the correct code
DELETE
FROM SecondaryCategory
WHERE EXISTS(select 1 from deleted where SecondaryCategory.[ArtistID] = DELETED.[ArtistID]) AND (SecondaryCategory.[ArtistGenreID] = DELETED.[ArtistGenreID])
)
Abhijit - http://abhijitmore.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply