Trigger to update column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger to update column

Author

Topic

InNomina
Starting Member

40 Posts

Posted - 2011-08-19 : 12:08:10

Ok,

I am new to triggers....Very new

I have a table called Folders which has 2 columns.

The first column called FolderStatusId has a pull down menu which has several items one of which is Closed.
When closed is selected for that row I want to update the 2nd column named ClosedDate to reflect the date that Closed was chosen from the first table.

Any help is appreciated to get me started.

Thank you in advance!
-------------------------
"The trouble with doing something right the first time is that nobody appreciates how difficult it is"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 12:13:08

something like


CREATE TRIGGER Trg_Update ON Folders
FOR UPDATE
AS
BEGIN
UPDATE f
SET f.ClosedDate=GETDATE()
FROM Folders f
INNER JOIN INSERTED i
ON i.PK = f.PK
AND i.FolderStatusId= <id for Closed>
END

pk should be replaced by your actual primary key column(s)
also set nested triggers option to false for the db.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

InNomina
Starting Member

40 Posts

Posted - 2011-08-19 : 13:16:42

quote:
Originally posted by visakh16

something like

CREATE TRIGGER Trg_Update ON Folders
FOR UPDATE
AS
BEGIN
UPDATE f
SET f.ClosedDate=GETDATE()
FROM Folders f
INNER JOIN INSERTED i
ON i.PK = f.PK
AND i.FolderStatusId= <id for Closed>
END

pk should be replaced by your actual primary key column(s)
also set nested triggers option to false for the db.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I will give this a shot, Thank you!

-------------------------
"The trouble with doing something right the first time is that nobody appreciates how difficult it is"

InNomina
Starting Member

40 Posts

Posted - 2011-08-19 : 14:32:06

So I tried this....

CREATE TRIGGER Trg_Update ON Folders
FOR UPDATE
AS
IF (update(DateClosed)
Begin
UPDATE f
SET f.ClosedDate=GETDATE()
FROM Folders f
INNER JOIN INSERTED i
ON i.Id = f.Id
AND f.FolderStatusId = 3
END

But I get this error...
sg 156, Level 15, State 1, Procedure Trg_Update, Line 5
Incorrect syntax near the keyword 'UPDATE'.
Msg 102, Level 15, State 1, Procedure Trg_Update, Line 11
Incorrect syntax near 'END'.

-------------------------
"The trouble with doing something right the first time is that nobody appreciates how difficult it is"

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-19 : 17:29:29

What are you trying to do with the IF statement?

Hey, it compiles.

InNomina
Starting Member

40 Posts

Posted - 2011-08-19 : 19:00:04

quote:
Originally posted by ajthepoolman

What are you trying to do with the IF statement?

Hey, it compiles.

If the column named status = closed then update the column named Close Date with the date it was closed.

-------------------------
"If you never fail, you're not trying hard enough"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:44:40

shouldnt it be this? ie update it to current date if its not already updated?


CREATE TRIGGER Trg_Update ON Folders
FOR UPDATE
AS
IF NOT(update(DateClosed))
Begin
UPDATE f
SET f.ClosedDate=GETDATE()
FROM Folders f
INNER JOIN INSERTED i
ON i.Id = f.Id
AND f.FolderStatusId = 3
END
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources