April 26, 2011 at 1:34 pm
Guys,
I need your help with triggers,
I need to create a trigger on the table
for Inserts and Updates with following logic:
If NickName is not blank than update FName with a NickName else keep FName the same
This is what I have, but I don't think it is correct:
FOR INSERT, UPDATE as
BEGIN
Update dbo.Employ
set
FName = ins.NickName
from Employ e, inserted ins
Where e.Id=ins.Id AND e.NickName IS NOT NULL
END
April 27, 2011 at 1:16 am
Hi,
first thing is: to make it more readable for me and the other viewers you should use the IFCode-Parts to identify sql code ... and in most cases it would be helpful if you post the table creation statement.
Now your question:
1. I would always use explicit joins and not comma separated tables, because of readability and performance. 🙂
2. You can simply update the inserted table because the data in the inserted table would be added to the Employ table after execution of this trigger.
I have changed your statement to the statement below:
CREATE TRIGGER tr_TriggerName
FOR INSERT, UPDATE as
BEGIN
Update ins
set FName = ins.NickName
from inserted ins
Where ins.NickName IS NOT NULL
END
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 27, 2011 at 3:35 am
Patrick_Fiedler (4/27/2011)
2. You can simply update the inserted table because the data in the inserted table would be added to the Employ table after execution of this trigger.
Well that's something I'd never read about or seen before, so I thought I'd learnt something new... afraid not, you can't do that. If you try, you get an explicit error telling you that you can't update the inserted or deleted tables...
Msg 286, Level 16, State 1, Procedure tr_TriggerName, Line 5
The logical tables INSERTED and DELETED cannot be updated.
April 27, 2011 at 4:22 am
Ian Scarlett (4/27/2011)
Patrick_Fiedler (4/27/2011)
2. You can simply update the inserted table because the data in the inserted table would be added to the Employ table after execution of this trigger.Well that's something I'd never read about or seen before, so I thought I'd learnt something new... afraid not, you can't do that. If you try, you get an explicit error telling you that you can't update the inserted or deleted tables...
Msg 286, Level 16, State 1, Procedure tr_TriggerName, Line 5
The logical tables INSERTED and DELETED cannot be updated.
Hi Ian,
sorry, it was my fault, I haven't tested it. But now I am back with a solution that is tested. 😎
create trigger tr_Trigger
on Employ
for insert, update
as
if Update(Nickname)
begin
Updatee
setFName = ins.NickName
frominserted ins
inner join
Employ e on ins.id=e.id
Whereins.NickName IS NOT NULL
end
GO
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 27, 2011 at 7:27 am
Question,
1.Why do you write if update?
You already have code before for update and insert?
2.If update works for update and insert?
Thank you
April 27, 2011 at 7:41 am
Krasavita (4/27/2011)
Question,1.Why do you write if update?
The update function returns true if the written column was inserted or updated. In this way you can prevent execution of the update statement if another column beside Nickname is changed. But if you also want to update FName in the case of updating FName you can delete the if update. It's more performance purpose if you have many columns.
Krasavita (4/27/2011)
You already have code before for update and insert?2.If update works for update and insert?
Yes, if it is inserted it would be like a update for this function.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 27, 2011 at 8:05 am
Thank you,
What is the difference to write After insert update and for insert, update?
I had to put this code inside another trigger on the same table,so if I have 2 update/insert
Is this corect
create trigger tr_Trigger
on Employ
for insert, update
as
if Update(hdate)
begin
Update e
set sdate = ins.hdate from inserted ins
join
Employ e on ins.id=e.id
Where ins.hdate IS NOT NULL
end
if Update(Nickname)
begin
Update e
set FName = ins.NickName
from inserted ins
join
Employ e on ins.id=e.id
Where ins.NickName IS NOT NULL
end
April 27, 2011 at 8:43 am
Also is it better for performance put inner join or just join
April 28, 2011 at 8:13 am
Yes, it is.
In relation to your question about the if update statement:
You don't have to take the if update statement, the update of the column is enough without check, because sql server wouldn't execute a trigger if the himself changed the values.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 28, 2011 at 8:17 am
In what case would I use if update?
Thank you
April 28, 2011 at 11:24 pm
For example if you have to create a trigger for a table with 100 columns and in your trigger you should only execute a statement if one special column would be updated.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 28, 2011 at 11:24 pm
For example if you have to create a trigger for a table with 100 columns and in your trigger you should only execute a statement if one special column would be updated.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 29, 2011 at 9:37 am
Thank you
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply