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

Help with Trigger

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-08-17 : 04:48:30

Hi,

I am writing a trigger for Updating a separate table (e.g. EmployeeCompanyHistory) when a column in the first table (Let's call it Employee) is updated.

For example, let's say the Employee Changed their LoginName (this is a PK), I would like to have a trigger that will change the employeeCompanyHistory's Loginname to the new Employee's LoginName where the LoginName is the OLD Employee's LoginName. How do I do this?

I am struck when I am trying to get the old value that is being updated and the new value that is inputted by users. any help is very much appreciated.

Create Trigger updateNameInEmployeeCompanyHistory

ON HumanResources.Employee

For Update as
BEGIN
IF Update([LoginName])
bEGIN

Declare @OldValue varchar(50)
Declare @NewValue varchar(50)

Set @OldValue = ??
Set @NewValue = ??

Update employeeCompanyHistory
SET LoginName = @NewValue
where LoginName = @OldValue

END
GO

in oracle I usually use :old or :new but is unsure the equilavent in MSSQL... :( Help is very much appreciated

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-17 : 05:30:08

make use of virtual tables

if the trigger is update-trigger make use of this

select top 1 @newValue=loginname from inserted

select top 1 @oldValue=loginname from deleted

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-08-17 : 05:31:34

Hi Senthil,

Thank you

Regards,

Eugene

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 05:52:13

what is the primary key for table HumanResources.Employee ?

quote:

select top 1 @newValue=loginname from inserted

select top 1 @oldValue=loginname from deleted

This trigger code does not handle situation where more than 1 rows are affected

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-17 : 06:30:37

quote:
Originally posted by khtan

what is the primary key for table HumanResources.Employee ?

quote:

select top 1 @newValue=loginname from inserted

select top 1 @oldValue=loginname from deleted

This trigger code does not handle situation where more than 1 rows are affected

KH
[spoiler]Time is always against us[/spoiler]

yes it only works for single row, remove top for multiple rows

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 08:40:03

quote:
remove top for multiple rows

you will still assign one value to the variable, and lost the rest of the value.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 08:45:27

Eugene,

It is best you handle these in the store procedure that perform the changing of loginname. You will not be able to associate the old value with the new value inside the trigger.

KH
[spoiler]Time is always against us[/spoiler]

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-08-17 : 21:50:04

KHTan,

thank you for your advise. You are right in this regards that only the top(1) will be stored and change when there are concurrent change request. I have taken your advise and am now changing the store procedure accordingly.

Triggers are not appropriate for this. :(

Regards,

Eugene

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources