Triggers - 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

General SQL Server Forums

New to SQL Server Programming

Triggers

Author

Topic

Mr Bronz
Starting Member

5 Posts

Posted - 2012-09-29 : 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

1974 Posts

Posted - 2012-09-30 : 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 - 2012-09-30 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-30 : 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.

Mr Bronz
Starting Member

5 Posts

Posted - 2012-10-01 : 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)

17689 Posts

Posted - 2012-10-01 : 05:21:27

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

KH
[spoiler]Time is always against us[/spoiler]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-01 : 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 - 2012-10-01 : 08:18:18

quote:
Originally posted by khtan

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

KH
[spoiler]Time is always against us[/spoiler]

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources