July 18, 2012 at 9:12 am
Hello, I need to create an insert trigger on a SQL Server 2005 database table that will set the inserted row date field to (CONVERT([varchar](10),getdate(),(112)). When I use the following code it seems to update all the rows in the table. I am used to being able to change the inserted value prior to the insert in Oracle. Here is what is not working for me:
CREATE TRIGGER [dbo].[tr_intr_ob_problem_list_]
ON [dbo].[ob_problem_list_]
FOR INSERT
AS
BEGIN
UPDATE ob_problem_list_
SET datefield = (CONVERT([varchar](10),getdate(),(112)))
FROM inserted i INNER JOIN ob_problem_list_ o ON i.person_id = o.person_id and i.seq_no = o.seq_no
END
Thank you,
David
July 18, 2012 at 9:50 am
i believe it's becasue you want to update the alias "o",
try this instead:
CREATE TRIGGER [dbo].[tr_intr_ob_problem_list_]
ON [dbo].[ob_problem_list_]
FOR INSERT
AS
BEGIN
UPDATE o --update the alias used below
--stay with date times: simply trim the time portion off of the getdate:
SET datefield = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
FROM inserted i
INNER JOIN ob_problem_list_ o
ON i.person_id = o.person_id
and i.seq_no = o.seq_no
END
Lowell
July 18, 2012 at 11:18 am
Or you could write it this way:
UPDATE ob_problem_list_
SET datefield = (CONVERT([varchar](10),getdate(),(112)))
FROM
inserted i
WHERE
i.person_id = ob_problem_list_.person_id and i.seq_no = ob_problem_list_.seq_no
END
Based on what it looks like you need to accomplich you'd probably be better off with a default constraint on the datefield column.
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 18, 2012 at 2:11 pm
CELKO (7/18/2012)
You are doing the wrongs things, the wrong way and have not given us enough information to help you. Want to try again?
two syntactically correct , workable examples were posted before your reply...i thought there was enough information about this specific issue to identify teh problem(all rows updating) and the code with which to correct it.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply