July 14, 2006 at 10:33 am
i am new to this.i currently work in asp/sql server.my requirement is a stored procedure which should do the function below.two difeerent tables in two different databases in two servers i suppose.but i can access both.now when i update or insert in first table in first db certain feilds(colunms) it should automatically update the same fields(colunms) in the second table in the second db.but when i delete from the first db it should not affect the second db.should i use a mix of trigger and stored procedure? .tried the trigger part but it shows error when accessing the second db.i have to upload this in realtime.do throw some light on this
July 14, 2006 at 10:56 am
here is the sample code...
use tempdb
create table junk (srno int primary key, descr varchar(30))
use sample
create table junk (srno int primary key, descr varchar(30))
create trigger tr_iu_junk on junk for insert, update
as
declare
@Action char(1)
begin
set nocount on
-- to capture the triggering event i.e insert /delete /update
if exists (
select top 1 'x' from inserted )
begin
if exists( select top 1 'x' from deleted)
set @Action = 'U' -- update
else
set @Action = 'I' -- insert
end
else
begin
if exists (select top 1 'x' from deleted)
set @Action = 'D' -- delete
end
if @Action = 'I'
insert into tempdb..junk
select * from inserted
else
Update p
set descr = i.descr
from
tempdb..junk p
join
inserted i
on
p.Srno = i.Srno
end
-- Verifying insert
insert into junk values (1, 'One')
select 'Tempdb',* from tempdb..junk
union all
select 'Sample',* from junk
-- Verifying update
update junk set
descr = 'OneTwo'
select 'Tempdb',* from tempdb..junk
union all
select 'Sample',* from junk
-- Verifying delete
delete from junk
select 'Tempdb',* from tempdb..junk
union all
select 'Sample',* from junk
drop table junk
use tempdb
drop table junk
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply