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
 General SQL Server Forums
 New to SQL Server Programming
 Triggers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mr Bronz
Starting Member

5 Posts

Posted - 09/29/2012 :  09:35:18  Show Profile  Reply with Quote
Hi to all

This is my first post so please be kind.

I know some sql but am no expert.

I need to write a trigger so that when a record in table A is Inserted/updated/Deleted
It is reflected in table B.

But I am only interesed in say 4 of the 29 columns in Table A

So let me see if I can sumerise this...

Table A|Col1|Col2|Col3|
row1 | aa | 1 | 5 |
row2 | bb | 0 | 4 |
row3 | cc | 0 | 4 |
row.n
-------------------------
Table b|Col1|Col2|
row1 | aa | 1 |
row2 | bb | 0 |
row3 | cc | 0 |
row.n


So if recored say row 2 is alterd it is reflected in table B

I understand that I could have used a view for what is needed but the client wants independent seperation between the 2 tables

How do I reflect the canges in table A to Table B

Thanks


I didnt do it! i just did what i was told!

chadmat
The Chadinator

USA
1974 Posts

Posted - 09/30/2012 :  02:24:52  Show Profile  Visit chadmat's Homepage  Reply with Quote
Not sure what the question is, looks like you already know that you need to use a trigger.

-Chad
Go to Top of Page

Mr Bronz
Starting Member

5 Posts

Posted - 09/30/2012 :  06:57:44  Show Profile  Reply with Quote
Hi Thanks for the reply

I dont know how to get the values that have just changed from the source table so that I can update/insert/delete in the destination table.

So the questions are :-

a)How do I determin what action it is being taken "update/insert/delete"?

b)How do I determin what cells have changed?.

c)How do I address the cells in question.

Thanks

I didnt do it! i just did what i was told!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/30/2012 :  18:57:56  Show Profile  Reply with Quote
To be able to do this reliably, you would need a primary key (consisting of one or more columns) in your tables. In the code below, I am assuming that col1 is the primary key column. If that is the case, you would create trigger for UPDATE, INSERT and DELETE as follows.
CREATE TRIGGER dbo.YourUpdateTriggerName ON dbo.TableA
FOR UPDATE
AS
UPDATE t2 SET 
	t2.col2 = i.col2,
	t2.col3 = i.col3,
	--other COLUMNS you have IN table2 here.
FROM
	Table2 t2
	INNER JOIN INSERTED i ON i.col1 = t2.col1;
GO

CREATE TRIGGER dbo.YourINSERTTriggerName ON dbo.TableA
FOR INSERT
AS
INSERT INTO Table2
SELECT
	col1,
	col2,
	col3,
	--other COLUMNS you may want IN table2 here.
FROM
	INSERTE;	
GO


CREATE TRIGGER dbo.YourDELETETriggerName ON dbo.TableA
FOR DELETE
AS
DELETE FROM t2
FROM
Table2 t2 INNER JOIN DELETED d ON d.col1 = t2.col1;

GO
In the trigger, you have access to two virtual tables - INSERTED and DELETED. For Insert statements, the DELETED table would be empty and for delete statements, the inserted table would be empty. For update statements, the inserted table would contain the "after" values and deleted table would contain the "before" values. Those are the tables I am making use of in the query above.

I wrote this query and have not tested it. So please test in dev environment to see if it does what you are expecting it to do.

Edited by - sunitabeck on 09/30/2012 18:58:22
Go to Top of Page

Mr Bronz
Starting Member

5 Posts

Posted - 10/01/2012 :  05:11:39  Show Profile  Reply with Quote
Hi there

Thank you so very much.

This is just what i was looking for.

I have just 2 simple questions regarding the code above

in the section "FROM
Table2 t2
INNER JOIN INSERTED i ON i.col1 = t2.col1;"

Can I assume t2 is table 2 (my destination table)
What will Table2 represent?

And last what will 'i' represent.

I just would like to totally understand what I am doing so that I can learn from it.

Many thanks


I didnt do it! i just did what i was told!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 10/01/2012 :  05:21:27  Show Profile  Reply with Quote
t2 and i are the table alias given to table2 and inserted


KH
Time is always against us

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 10/01/2012 :  05:23:13  Show Profile  Reply with Quote
hi,

1) Table2 is your destination table

2) 'i' refers the newly updated values. (INSERTED table contains the values that are being changed)

--
Chandu
Go to Top of Page

Mr Bronz
Starting Member

5 Posts

Posted - 10/01/2012 :  08:18:18  Show Profile  Reply with Quote
quote:
Originally posted by khtan

t2 and i are the table alias given to table2 and inserted


KH
Time is always against us





Oh I see so Table2 t2 is one and the same, so I only need my destination table in place of both these names...

And now I see where the alias is give "INNER JOIN INSERTED i ON i.col1 = t2.col1;"

I didnt see that before.

Many thanks for the leason guys

All the very best



I didnt do it! i just did what i was told!
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.09 seconds. Powered By: Snitz Forums 2000