July 30, 2008 at 12:29 pm
Got the trigger below but don't know what to do to concatenate the site column and resource column after an insert or update.
Can anyone show how?
CREATE TRIGGER dbo.trptAppointmentsINSERT
ON dbo.ptAppointments
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(resource) OR UPDATE(site)
BEGIN
-- Not sure what to do here...
END
END
GO
July 30, 2008 at 12:37 pm
I think you want something like this:
[font="Courier New"]SELECT
I.resource + ' ' + I.site
FROM
inserted I
[/font]
Inside your IF block.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 12:59 pm
How would I modify this to work for both the insert and update and am I doing this
correctly or not? I want to concatenate the resource and site columns into resource
on each insert or update. This works on INSERT, I've tested it.
CREATE TRIGGER dbo.trptAppointmentsINSERT
ON dbo.ptAppointments
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(resource) OR UPDATE(site)
BEGIN
DECLARE @PID varchar(255)
DECLARE @Date datetime
DECLARE @Resource varchar(255)
DECLARE @Site varchar(255)
SET @Resource = (SELECT resource FROM inserted)
SET @Site = (SELECT site FROM inserted)
SET @PID = (SELECT PID FROM inserted)
SET @Date = (SELECT date FROM inserted)
UPDATE ptAppointments SET resource = @Resource + ' - ' + @Site
WHERE PID = @PID AND
Date = @Date
END
END
GO
July 30, 2008 at 12:59 pm
Or
update MyTable
set col = sitename + resource
from inserted i
where i.pk = MyTable.pk
July 30, 2008 at 1:04 pm
Steve Jones - Editor (7/30/2008)
Orupdate MyTable
set col = sitename + resource
from inserted i
where i.pk = MyTable.pk
UPDATE ptAppointments SET resource = I.resource + I.site FROM inserted I
WHERE ptAppointments.PID = I.PID AND
ptAppointments.Date = I.Date
Result:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'inserted'.
July 30, 2008 at 1:26 pm
Here is something I think will work:
[font="Courier New"]CREATE TRIGGER dbo.trptAppointmentsINSERT
ON dbo.ptAppointments
AFTER INSERT, UPDATE[/b]
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(resource) OR UPDATE(site)
BEGIN
UPDATE ptAppointments
SET resource = I.Resource + ' - ' + I.Site
FROM
inserted I
WHERE
PID = I.PID AND
Date = IDate
END
END
[/font]
I bolded the section that gets you updates as well. You need to be VERY careful using variables in triggers because triggers act on SETS of data so so a batch insert or update will populate the inserted/deleted tables with multiple rows and using variables limits your trigger to working on the first row returned.
So if I were to do:
[font="Courier New"]UPDATE ptAppointments
SET resource = I.Resource + ' - ' + I.Site
[/font]
which updates every row in the table your trigger using variables would only handle the first on it gets to and you are not guaranteed order.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 1:29 pm
You need to add After insert, update if need for both insert and update...
CREATE TRIGGER dbo.trptAppointmentsINSERT
ON dbo.ptAppointments
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(resource) OR UPDATE(site)
BEGIN
DECLARE @PID varchar(255)
DECLARE @Date datetime
DECLARE @Resource varchar(255)
DECLARE @Site varchar(255)
SET @Resource = (SELECT resource FROM inserted)
SET @Site = (SELECT site FROM inserted)
SET @PID = (SELECT PID FROM inserted)
SET @Date = (SELECT date FROM inserted)
UPDATE ptAppointments SET resource = @Resource + ' - ' + @Site
WHERE PID = @PID AND
Date = @Date
END
END
GO
-V
July 30, 2008 at 1:38 pm
OK, got it working based on Jack's last post.
Thanks a lot...
July 30, 2008 at 5:10 pm
MrBaseball34 (7/30/2008)
UPDATE ptAppointments SET resource = I.resource + I.site FROM inserted I
WHERE ptAppointments.PID = I.PID AND
ptAppointments.Date = I.Date
Result:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'inserted'.
The "inserted" and "deleted" tables are really psuedo-tables that only exist inside of a trigger, during activation. You cannot bench-test the query alone in a query window, the way that you can many other queries.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply