SQLSERVER TRiggers

Question

SQLSERVER TRiggers

padmapatibandla-858284

SSC Enthusiast

Points: 103
More actions
October 15, 2008 at 11:36 am

#199381

I have a table,if 2 of its columns r updated , the 3rd column should return a null value.
can any one help me to write a trigger on this

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Why not handle this in the original transaction? If the user changes the 2 columns then send the 3rd column to the database as a null value.

What if the update doesn't actually change the data? As far as triggers in SQL Server are concerned

Update tableA

Set columnA = ColumnA

is an update and the data was not changed.

Are you concerned about changes in case ('jack' to 'Jack')? Depending on collation that can be the same or different.

You might want to check out this similar thread: http://www.sqlservercentral.com/Forums/Topic585735-1291-1.aspx

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

padmapatibandla-858284 SSC Enthusiast Points: 103 More actions · Answer 2

padmapatibandla-858284

SSC Enthusiast

Points: 103

October 15, 2008 at 3:20 pm

#885540

Thank you for your answer Jack.

sheeba 88002 SSC Rookie Points: 32 More actions · Answer 3

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]

FOR UPDATE

AS

declare @empid int;

declare @empname varchar(100);

declare @empsal decimal(10,2);

declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;

select @empsal=i.Emp_Sal from inserted i;

if update(Emp_Name)

set @empsal=null;

if update(Emp_Sal)

set @empsal=null;

insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER UPDATE Trigger fired.'

GO

Checkout this trigger.u can get the third column will be null.and send me feedback..

Jack Corbett SSC Guru Points: 184393 More actions · Answer 4

Sheeba,

What happens in your trigger if there is a set-based update? Like:

Update Employee_Test

Set Emp_Sal = Emp_Sal * 1.10

Where

Emp_ID <= 10

I can tell you what will happen, 1 row will be logged not the 10 that were updated (assuming Emp_ID starts with 1 and there are no gaps).

You need to be careful with this with triggers in SQL Server. Pretty much anytime there is a variable used in a trigger it will not correctly handle a set-based update.

Also, as I mention in my first post, if a column is included in an Update statement, bur not changed then the UPDATE function will consider it updated.

So in your example setting the Emp_Name = Emp_Name will still cause the logging with a NULL value in the third 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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 5

Not a big deal. You can handle everything easily in a trigger:

CREATE TRIGGER trigger_name

ON table_name

AFTER UPDATE

AS

--if no rows were actually UPDATEd, exit immediately

IF @@ROWCOUNT = 0

RETURN

--only need to do another UPDATE if column1 and column2 were changed

IF UPDATE(column1) AND UPDATE(column2)

BEGIN

UPDATE t

SET column3 = NULL

FROM table_name t

INNER JOIN inserted i ON i.keycol = t.keycol

INNER JOIN deleted d ON d.keycol = t.keycol

--make sure that column1 and column2 actually changed

WHERE ISNULL(i.column1, 'zzz') <> ISNULL(d.column1, 'zzz')

AND ISNULL(i.column2, 'zzz') <> ISNULL(d.column2, 'zzz')

END --IF

Scott Pletcher, SQL Server MVP 2008-2010

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 6

Of course if column1 and/or column2 are not char data types, you will need to change 'zzz' to something that matches their data type(s).

Scott Pletcher, SQL Server MVP 2008-2010

GSquared SSC Guru Points: 260824 More actions · Answer 7

scott.pletcher (11/30/2010)
Of course if column1 and/or column2 are not char data types, you will need to change 'zzz' to something that matches their data type(s).

Or use "nullif(i.col,d.col) is not null", instead of more complex comparisons.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon