SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to make a Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrSmallTime
Starting Member

United Kingdom
26 Posts

Posted - 12/25/2013 :  08:39:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/25/2013 :  23:15:50  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/26/2013 :  03:56:34  Show Profile  Reply with Quote
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
Go to Top of Page

MrSmallTime
Starting Member

United Kingdom
26 Posts

Posted - 12/26/2013 :  12:41:56  Show Profile  Reply with Quote
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.
Go to Top of Page

MrSmallTime
Starting Member

United Kingdom
26 Posts

Posted - 12/26/2013 :  13:17:31  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/28/2013 :  03:55:24  Show Profile  Reply with Quote
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
Go to Top of Page

MrSmallTime
Starting Member

United Kingdom
26 Posts

Posted - 12/30/2013 :  07:11:02  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000