How to make a 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

General SQL Server Forums

New to SQL Server Programming

How to make a Trigger

Author

Topic

MrSmallTime
Starting Member

32 Posts

Posted - 2013-12-25 : 08:39:55

Hi,

I Need help making my first DML trigger.

I want to make null fields from tables 2 and 3 (if data exists in those fields\tables) on deleting a record in table 1
The table I will be deleting from is tblNotes that has a primary key NotesID

The two related tables are
TblLinks and the related field to NotesID is LnkNotesID
TblDo and the related field to NotesID is DoNotesID

I don't want to delete the related records just turn the related fields to NULL

Not sure if it’s better to make an Instead Trigger or an Update trigger, Haven’t made a trigger before and I've bee reading-up a little but still not sure how to accomplish this in the correct manner.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-12-25 : 23:15:50

I think no need of triggers here...
You can make use of ON DELETE SET NULL option
http://www.sqlteam.com/article/using-set-null-and-set-default-with-foreign-key-constraints

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-26 : 03:56:34

The easiest way to implement this is previous suggestion. However in case you dont have rights to mess with existing FK constraints the trigger should be this


CREATE TRIGGER Trig_Notes
ON tblNotes
INSTEAD OF DELETE
AS
BEGIN
UPDATE l
SET l.LnkNotesID = NULL
FROM TblLinks l
INNER JOIN DELETED d
ON d.NotesID = l.LnkNotesID 

UPDATE dn
SET dn.DoNotesID = NULL
FROM TblDo dn
INNER JOIN DELETED d
ON d.NotesID = dn.DoNotesID 

DELETE t
FROM tblNotes t
JOIN DELETED d
ON d.NotesID = t.NotesID
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-12-26 : 12:41:56

Sorry for the late reply, was away from my PC

Many thanks, I'll be testing later today and consuming the technique to memory.

Should have guessed visakh16 would come to my rescue again

Many thanks for the suggestion bandi but as I'm fairly new to sql server I thought I'd wet my feet with a trigger. Also there are multiple relationships to TblLinks and TblDo and Management Sutdio isn't allowing me to set null on delete. Can't remember the exact message but is was something on the lines of 'may cause cycles or multiple cascade paths'. But good suggestion.

MrSmallTime
Starting Member

32 Posts

Posted - 2013-12-26 : 13:17:31

I didn't realise that tables with a relationship can't have a trigger

TblNotes has NO relationships with tblLinks or TblDo but HAS a has Foreign key relationship to another table with Delete and Update set to cascade.

so I got the message
"Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'Trig_Notes' on table 'tblNotes'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE."

hmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 03:55:24

quote:
Originally posted by MrSmallTime

I didn't realise that tables with a relationship can't have a trigger

TblNotes has NO relationships with tblLinks or TblDo but HAS a has Foreign key relationship to another table with Delete and Update set to cascade.

so I got the message
"Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'Trig_Notes' on table 'tblNotes'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE."

hmmm

yep..if you've FK defined with CASCADE options you cant have instead of triggers on them. if you already have fk defined with cascading option then whats the purpose of this requirement?
I think then only thing you need to do is to modify constraints to make it ON DELETE SET NULL
see
http://www.sqlteam.com/article/using-set-null-and-set-default-with-foreign-key-constraints

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-12-30 : 07:11:02

Actually the relationship to tblNotes that causing the issue is with a separate table not with TblLinks or TblDo and I had imagined that deleting a record from tblNotes would allow a valid trigger, but unfortunately not. I eventually gave up trying to do this from SQL Server and handled it from the Front-End. Not the ideal choice as it impacts on portability, just another sacrifice in a growing list of sacrifices.

However I've save the code you kindly produced as it'll help me greatly as the project progresses. Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources