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

SQL Server 2008 Forums

SQL Server Administration (2008)

Trigger

Author

Topic

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-19 : 02:50:27

I am having tables A and B ..Both tables are having same structure
...
I need to write a trigger on table A ..that when ever insert ,update delete happen on table A ..same data will be insert update or delete from table B also ..

Like both tables will have same data..

how to write it ..

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-19 : 02:52:54

I am having tables A and B ..Both tables are having same structure
...
I need to write a trigger on table A ..that ..whenever insert ,update delete happen on table A ..same data will be insert update or delete in table B also ..

Like both tables will have same data..

how to write it ..any idea

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-19 : 02:56:40

why do you need to do this ? Why do you need 2 tables that is identical ?

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

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-19 : 03:09:10

I need second table (B) with same data having table (A) .

The table B will be refered by some other program ...this program can i refer to table A directely ...some ting is there..

can you help me write such trigger ..on table A ...fire on very insert update delete ..it will do same to table B also ..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-19 : 03:25:17

can you just use a view instead ?

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

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-19 : 04:43:14

NO..because we will use table B data for other purpose..it is just
like we import production data into testing database and use this for testing ..

i have to wirte trigger ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-19 : 06:32:13

I still don't quite understand your actual requirement.

quote:
because we will use table B data for other purpose

What do you need to do that the View can't provide ? In fact, why can't you just use table A directly.

for trigger, read up trigger in Books On Line
http://msdn.microsoft.com/en-us/library/ms189799.aspx

And also
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
http://www.sqlteam.com/article/an-introduction-to-triggers-part-ii

in short, you will need 3 trigger for Insert, Update & Delete. Here is some example


-- Insert Trigger
insert into tableb ( . . . )
select . . .
from   inserted i

-- Update Trigger
update b
set    somecol = i.somecol,
       . . . 
from   tableb b inner join inserted i
       on b.pk = i.ok

-- Delete Trigger
delete b
from   tableb b inner join deleted d
       on b.pk = d.pk

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

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-19 : 08:15:47

ok..

the table A is in production database and table b is in testing database ..we want to use table A data for some analyaing ..but table A is in pro.. so we have to take a copy of A .

In oracle we can use one trigger for insert,update like

if inserting then
insert -----
end if
if updateing then
update ----
end if

can we write same in the sql server also .. in one trigger ..?

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 09:43:29

"can we write same in the sql server also .. in one trigger ..? "

Yes. There are two pseudo tables [deleted] and [inserted] which have the data Before/After the action.

For INSERT then [deleted] will be empty
For UPDATE then [deleted] will have old data and [inserted] new data
For DELETE then [deleted] will have old data (just deleted) [inserted] will be empty.

So you can construct INSERT / UPDATE / DELETE statements based on the data in [inserted] / [updated]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources