UPDATE Trigger Help - 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

UPDATE Trigger Help

Author

Topic

genemcsween
Starting Member

2 Posts

Posted - 2012-08-07 : 10:55:20

I'll start by saying this is my fist trigger and I am very new to SQL from a developer's prospective. I'm trying to write a trigger that will update a date field whenever another field is marked False (bit). Information below.

DB Name - MACS_Users
Table Name - Users
Fields I care about - priKey (int, PK) isActive (bit), Inactive_Date (datetime)
What I want - Update Inactive_Date field with GetDate() when isActive is flipped to False.
What I have so far (not sure if any is right):


CREATE TRIGGER TRG_UpdateInactive_Date ON Users
AFTER UPDATE
AS
IF UPDATE(isActive)
BEGIN
	DECLARE @oldActive bit, @newActive bit
	SELECT @oldActive = isActive FROM deleted.Users
	SELECT @newActive = isActive FROM inserted.Users
	IF @newActive = 'False' AND @oldActive = 'True'
	BEGIN
		UPDATE Users 
		SET Inactive_Date=GetDate()

I get lost in the UPDATE statement. I know I need a WHERE or FROM or something on the end of that to tell it which records to update but I'm not sure what it should look like. I'm sure this will be simple for someone who knows what they are doing but I'm scratching my head. Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 11:40:00

[code]
CREATE TRIGGER TRG_UpdateInactive_Date ON Users
AFTER UPDATE
AS
BEGIN
UPDATE t
SET t.Inactive_Date = GETDATE()
FROM Users t
INNER JOIN INSERTED i
ON i.priKey = t.priKey
INNER JOIN DELETED d
ON d.priKey = i.priKey
WHERE i.isActive=0
AND COALESCE(i.isActive,1) <> COALESCE(d.isActive,1)
END

also please ensure nested triggers setting is false for the instance and db
[/code]

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

genemcsween
Starting Member

2 Posts

Posted - 2012-08-07 : 12:43:54

Thank you very much for this. Could you please explain it a little? What exactly are the t and i in the code? Are they server constants/variables or are you just creating them on the fly here?

UPDATE t - What does this line do/mean?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 12:56:40

quote:
Originally posted by genemcsween

Thank you very much for this. Could you please explain it a little? What exactly are the t and i in the code? Are they server constants/variables or are you just creating them on the fly here?

UPDATE t - What does this line do/mean?

t and i are aliases ie short names for tables so that you dont have to repeat it everywhere

the t in UPDATE suggests that its table Users with name t thats to be updated

suggest you to refer to basics of sql programming from here to get an idea on basic concepts

http://www.w3schools.com/sql/default.asp

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources