| Author |
 Topic  |
|
|
Mr Bronz
Starting Member
5 Posts |
 Posted - 09/29/2012 : 09:35:18
|
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
1961 Posts |
Posted - 09/30/2012 : 02:24:52
|
Not sure what the question is, looks like you already know that you need to use a trigger.
-Chad |
 |
|
|
Mr Bronz
Starting Member
5 Posts |
Posted - 09/30/2012 : 06:57:44
|
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! |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/30/2012 : 18:57:56
|
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 |
|
|
|
Mr Bronz
Starting Member
5 Posts |
Posted - 10/01/2012 : 05:11:39
|
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! |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/01/2012 : 05:21:27
|
t2 and i are the table alias given to table2 and inserted
KH
Time is always against us
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 10/01/2012 : 05:23:13
|
hi,
1) Table2 is your destination table
2) 'i' refers the newly updated values. (INSERTED table contains the values that are being changed)
--
Chandu |
|
|
|
Mr Bronz
Starting Member
5 Posts |
Posted - 10/01/2012 : 08:18:18
|
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! |
|
|
| |
Topic |
|
Triggers
