January 13, 2009 at 2:12 pm
"ApprovedBy" is a field in my table. When this field is not empty or null during an insert, I want to fill in another column called ApprovedDate with the current date.
When I try to run it, I get this error:
Msg 102, Level 15, State 1, Procedure Insert_ApprovedDate, Line 12
Incorrect syntax near ')'
:crying:
CREATE TRIGGER Insert_ApprovedDate
ON Request
FOR INSERT
AS
IF EXISTS (SELECT ApprovedBy FROM inserted)
BEGIN
DECLARE @ab varchar(50)
SET @ab = (SELECT ApprovedBy FROM inserted)
If LEN(@ab) <> NUll
insert into Request(dateApproved) values(getdate())
January 13, 2009 at 2:26 pm
More like this:
CREATE TRIGGER Insert_ApprovedDate
ON Request FOR INSERT
AS
Update r
Set dateApproved = getdate()
From Request r
Join inserted i ON i.PK = r.PK
Where r.ApprovedBy IS NOT NULL
And LEN(i.dateApproved) > 0
[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]
January 14, 2009 at 5:46 am
I changed the r.PK to r.requestID but it did not insert a date in the dateApproved column even though there was a value for ApprovedBy.
Below is the trigger I used:
CREATE TRIGGER Insert_ApprovedDate
ON Request FOR INSERT
AS
Update r
Set dateApproved = getdate()
From Request r
Join inserted i ON i.requestid = r.requestid
Where r.ApprovedBy IS NOT NULL
And LEN(i.dateApproved) > 0
January 14, 2009 at 5:54 am
I think you have to remove the additional condition in WHERE clause....
CREATE TRIGGER Insert_ApprovedDate
ON Request FOR INSERT
AS
Update r
Set dateApproved = getdate()
From Request r
Join inserted i ON i.PK = r.PK
Where r.ApprovedBy IS NOT NULL
-- And LEN(i.dateApproved) > 0 -- This line should be removed
--Ramesh
January 14, 2009 at 6:05 am
Hi,
It worked. Thank you very much.
Problem was I should be checking for
the Len(ApprovedBy) not Len(dateApproved).
Thank you one more time.
January 14, 2009 at 6:51 am
Oops, that was my mistake in the solution that I posted.
[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]
January 14, 2009 at 6:58 am
Hey, it's all good. I should have caught it the first time.
Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply