SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 begginer Trigger question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
211 Posts

Posted - 02/22/2013 :  06:16:00  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi All,

This first time i use a trigger.
what i want to accomplish is if a customer change there details.
i copy the Original details to another table.
and only the record that was changed.

I had this but that did work

ALTER TRIGGER TR_Customer_original
ON dbo.T_Customer
for UPDATE
AS
BEGIN
INSERT INTO dbo.T_Customer_Changed
SELECT T_Customer.*
FROM T_Customer
SET NOCOUNT ON;

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 02/22/2013 :  06:48:15  Show Profile  Visit webfred's Homepage  Reply with Quote
At trigger runtime there are "tables" named deleted and inserted.
In case of Update the new data is in inserted and the old data is in deleted.
So in your example you have to select your data from deleted instead of T_Customer.

btw. SET NOCOUNT ON should be the first and not the last statement...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

mike13
Posting Yak Master

Netherlands
211 Posts

Posted - 02/22/2013 :  06:59:16  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
hi webfred,

thanks for the info.
But like i said I'm beginner with trigger and know idea how to accomplish what you just explained.

could you give me a syntax example please

thanks a lot,

Mike
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 02/22/2013 :  07:15:03  Show Profile  Visit webfred's Homepage  Reply with Quote
-- create the example tables
create table t_customer(id int, custname varchar(255))
create table t_customer_changed(id int, custname varchar(255))

-- create the trigger
create trigger t_customer_change_track 
on t_customer
for update
as
begin
set nocount on
insert t_customer_changed
select * from deleted
set nocount off
end


-- create some testdata
insert t_customer(id, custname)
select 1, 'mike' union all
select 2, 'Fred'

-- show testdata
select * from t_customer

select * from t_customer_changed

-- do the update
update t_customer
set custname = 'Mike13' where id=1

-- show testdata
select * from t_customer

select * from t_customer_changed



Too old to Rock'n'Roll too young to die.
Go to Top of Page

mike13
Posting Yak Master

Netherlands
211 Posts

Posted - 02/22/2013 :  08:47:31  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi webfred,


I didnit need everything just the trigger would have been enough ;-)
But thanks a lot !

Danku
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 02/22/2013 :  08:51:02  Show Profile  Visit webfred's Homepage  Reply with Quote
One thing inside the trigger should be done a better way:

I have posted:
set nocount on
insert t_customer_changed
select * from deleted
set nocount off

better:
set nocount on
insert t_customer_changed(id, custname)
select id, custname from deleted
set nocount off

It is never a good idea to use * instead of the column names...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

mike13
Posting Yak Master

Netherlands
211 Posts

Posted - 02/22/2013 :  10:56:18  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
hi webfred,

I understand, it is just a lot of fields ;-)
thats why i used the * in my question

tnx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000