sql Trigger question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

sql Trigger question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-19 : 15:39:11

I wrote the follow update trigger, please let me know if this okay or do I need to make any changes. Thanks.

Create trigger dbo.updatetrigger
on dbo.Employee
After Update
AS
Begin
set nocount on;
Insert into dbo.EmployeeModified
(emp_id,dept_id,firstname,lastname,address_line_1,city,datetimelog)
Select i.emp_id
,i.dept_id
,case when i.firstname <> d.firstname then i.firstname else d.firstname End
,case when i.lastname <> d.lastname then i.lastname else d.lastname End
,case when i.address_line_1 <> d.address_line_1 then i.address_line_1 else d.address_line_1 End
,case when i.city <> d.city then i.city else d.city End
,getdate()
from Inserted i
left join Deleted d
on i.emp_id = d.emp_id

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-19 : 16:26:41

I changed the trigger code, please review and let me know if it's okay. Thanks.

Create trigger dbo.updatetrigger
on dbo.Employee
After Update
AS
Begin
set nocount on;
Insert into dbo.EmployeeModified
(emp_id,dept_id,firstname,lastname,address_line_1,city,datetimelog)
Select i.emp_id
,i.dept_id
,i.firstname
,i.lastname
,i.address_line_1
,i.city
,getdate()
from Inserted i

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 09:57:30

looks fine if you want is to capture the new values.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-20 : 09:59:30

You didn't state what the overall objective (it appears rather obvious) so I'm guessing you're simply looking for a code review??? Outside of a missing End statement after "from Inserted i" the code looks fine. I'm not thrilled with your naming convention (or lack of), but that's just me. Is there anything more you're looking for?

edit - Visakh, do you EVER sleep?

Terry

-- Procrastinate now!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:06:12

quote:
Originally posted by tosscrosby

You didn't state what the overall objective (it appears rather obvious) so I'm guessing you're simply looking for a code review??? Outside of a missing End statement after "from Inserted i" the code looks fine. I'm not thrilled with your naming convention (or lack of), but that's just me. Is there anything more you're looking for?

edit - Visakh, do you EVER sleep?

Terry

-- Procrastinate now!

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-20 : 11:26:00

The source of data that is going into dbo.Employees table is a
sql statement. I need to update the dbo.Employees table, only
if there is change in data.
I have the follow questions:
1. How can I check the data change and update the employees table accordingly as I have around 30 columns
in the Employees table.
Please send me a example for my reference.

2. I have written the following triggers, can I combine the Insert and update into one trigger?
Please suggest if any code changes needs to be done.

Create Trigger dbo.Inserttrigger
on dbo.Employees
After Insert
AS
Begin
Set nocount on;
Insert into dbo.EmpIns_log
(EmpID, deptid,address1,city,state)
Select I.EmpID, I.deptid, I.address1,I.city,I.state
from Inserted I

Create trigger dbo.updatetrigger
on dbo.Employees
After Update
As
Begin
Set no count on;
Insert into dbo.Empupd_log
(EmpID, deptid,address1,city,state)
Select I.EmpID, I.deptid, I.address1,I.city,I.state
from Inserted I

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:01:15

1.do join with Employees on primary ke ad check for each field <> field from sql statment

2. you can combine them into single trigr

BJM RAO
Starting Member

20 Posts

Posted - 2009-03-20 : 13:20:06

UR code is almost fine, missed out end at the last , Instead of insert write for insert, update or delete - use ur code and join both tables with primary key.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-23 : 12:57:22

I am trying to use CHECKSUM function to check each column of the table if it's changed, I created a sample table emp(original table) and staging table "emp_staging", could you please correct the code below, as this code is not doing the update which I want to, I think there is some problem with the where clause:

SET NOCOUNT ON
UPDATE a
SET a.empname = staging.empname
FROM dbo.emp a
inner join dbo.emp_staging staging
on a.empid = staging.empid
WHERE a.empid = CHECKSUM(staging.empid,staging.empname,staging.empph);
GO

SELECT *
FROM dbo.emp;
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-23 : 13:01:36

use COLUMNS_UPDATED function instead of CHECKSUM to check whether particular columns are changed.

http://www.databasejournal.com/features/mssql/article.php/1479821/Using-ColumnsUpdated-in-a-Trigger.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources