March 20, 2009 at 9:14 am
I have written the following triggers, please let me know if the follow code is okay:
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
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.
Do I need to use the trigger to update the dbo.Employees table?
Thanks.
March 20, 2009 at 9:27 am
Those look basically okay. Looks like they should do what you need.
If you want more data on the subject of logging, take a look at these two articles:
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
- 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
March 20, 2009 at 10:11 am
They look okay to me as far as coding goes. In addition to the articles GSquared posted you may want to check out this one[/url] too.
I do have a question about the update trigger, you are only inserting the new data into EmpUpd_log so you can't see what the change was. Is this the behavior you are looking for?
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
March 20, 2009 at 2:08 pm
Jack Corbett (3/20/2009)
I do have a question about the update trigger, you are only inserting the new data into EmpUpd_log so you can't see what the change was. Is this the behavior you are looking for?
If you have the original insert, and every update since then, you can reconstruct the history of the data without having a specific record from the "deleted" table.
Better yet, in most cases, is don't log inserts, just log the "deleted" table for updates and deletes, and then the current state of the row is your new data. Little bit more complex to wrap your head around, but more efficient for the database.
- 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
March 20, 2009 at 2:15 pm
GSquared (3/20/2009)
Better yet, in most cases, is don't log inserts, just log the "deleted" table for updates and deletes, and then the current state of the row is your new data. Little bit more complex to wrap your head around, but more efficient for the database.
This is how I do it so the method of logging all the inserts is harder for me to wrap my head around. I also log who made the change.
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
March 20, 2009 at 2:25 pm
Jack Corbett (3/20/2009)I also log who made the change.
Pretty standard. Gotta agree with you.
- 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
March 20, 2009 at 2:44 pm
Here is my requirement:
1. I have a sql statement which is a source, I need to load this sql statement's output into Table1 which has 30 columns.
2. I need to insert data into Table1 if it's new data and update Table1 if there are any data changes ONLY.
3. In order to maintain the history of the data, I need to log all the new inserts into a TableIns_Log table
4. To maintain all the updates to Table1, I need to log all the updated data into a Tableupd_Log table
So I was planning to do the following steps:
Step1. Load the data from SQL into a staging table
--I only want to update if anything has changed, how do I check what has changed????
Step2. Update the data in Table1 as follows:
Update Table1 t1
Set
t1.col1 = staging.col1,
t1.col2 = staging.col2,
t1.col3 = staging.col3,
t1.col4 = staging.col4,
.....
....
from Table1 t1
inner join staging
on t1.ID = staging.ID
Step3. Insert:
Insert into table1
Select * from staging
where not exists (select ID from table1 t1 where t1.ID = Staging.ID)
Step4. Writing the Insert and update trigger on Table1, the trigger code has already been posted.
In the above step2 update, I only want to update if anything has changed, how do I check what has changed in Table1 which has 30 columns in it????
Thanks.
March 22, 2009 at 6:11 pm
Please let me know any ideas? Thanks.
March 23, 2009 at 7:12 am
There are a couple of ways to check if anything has changed. You could run a checksum on each row, and compare the checksum from the staging table with the checksum for the target table. You could include "where stagingtable.Col1 != realtable.Col1 or stagingtable.Col2 != realtable.Col2 or ...". Those are the ones I can think of straight off the bat. Shouldn't be too hard to build either one.
With what you're looking at doing, I'd be more inclined to put the logging into the proc, instead of in a separate trigger. Unless you're logging other stuff too. Makes it easier to document and maintain the process.
- 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
March 23, 2009 at 10:53 am
I am trying to use CHECKSUM function, 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
March 23, 2009 at 11:50 am
Needs to be:
WHERE CHECKSUM(a.empid,a.empname,a.empph) != CHECKSUM(staging.empid,staging.empname,staging.empph);
Try that, see if it gets what you need.
- 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
March 23, 2009 at 4:02 pm
Great, it worked!! You are awesome!!
March 24, 2009 at 7:10 am
Glad I could help.
- 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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply