Stored Procedures or what? 5

[RohanP]

Hi there,
There are 2 Databases is made in Sql Server 7 residing on same server. Both r mirror image of each other. having common tables.
One of the Table is AgentMaster residing on both the Databases.

If a row in DB1's AgentMaster is updated, suppose the address of Agent is changed, the changes should reflect in DB2's AgentMaster too. Even If a record is Inserted in DB1, DB2 should also have that inserted record. What should I use for this operation Oracle has Triggers for this operation but what should I use in Sql Server? Can anyone provide me with a example for the same??

DB1-AgentMaster
ID, Name, Addess, TDSRate, Comm
DB2-AgentMaster
ID, Name, Addess, TDSRate, Comm

Help will be appriciated ______________________________
- Regards Rohan (India,Mumbai)

 

[sguslan]

Use triggers for Insert/update/delete ...

Below is a great simple resource ..

http://www.easy-sql-server.com/index.asp?subject=triggers&page=1

 

[osjohnm]

Hi RohanP

Here is a script to create 3 triggers in DB1, insert, delete, update.

I have updated all columns in the update trigger, you could use IF UPDATE(column)instead for each column.

The triggers use the deleted and inserted tables which are only available during trigger execution.

I haven't tested these but they should all work. For the life of me I couldn't think of a better way to delete records, so thats why I used an IN statement.

--- triggers

create trigger insert_agentmaster
for insert
as
insert into db2.dbo.agentmaster (id, name, address, tdsrate, comm
select id, name, address, tdsrate, comm from db1.dbo.inserted
go

create trigger delete_agentmaster
for update
as
update db2.dbo.agentmaster set id = i.id, name = i.name, address = i.address, tdsrate = i.tdsrate, comm = i.comm
from db2.dbo.agentmaster a, db1.dbo.inserted i
where i.id = a.id
go

create trigger delete_agentmaster
for delete
as
delete from db2.dbo.agentmaster where id in
(select id from db1.dbo.deleted)
go

Hope this helps

John

 

[RohanP]

hi osjhonm
I created 2 tables inserted & deleted in db1, after making some changes in ur statements triggers were created successfully, but they r not doing the job they have to.

these r the minor changed statements -

create trigger insert_agentmaster
on agentmaster
for insert as
insert into db2.dbo.agentmaster (id, name, address, tdsrate, comm)
select [id], [name], address, tdsrate, comm from db1.dbo.inserted
go

create trigger update_agentmaster
on agentmaster
for update
as
update db2.dbo.agentmaster set name = i.name, address = i.address, tdsrate = i.tdsrate, comm = i.comm
from db2.dbo.agentmaster a, db1.dbo.inserted i
where i.id = a.id
go

create trigger delete_agentmaster
on agentmaster
for delete
as
delete from db2.dbo.agentmaster where id in
(select id from db1.dbo.deleted)
go

what may be the problem???? ______________________________
- Regards Rohan (India,Mumbai)

 

[tlbroadbent]

Rohan,

You said that you created the tables deleted and inserted in DB1. If you created those tables then you don't understand TRIGGERs and should do some additional study. The deleted and inserted tables are virtual tables that are only available in TRIGGERs. You don't create these tables. SQL Server creates them. Drop the tables and tehn test your TRIGGERs.

Though John's suggested TRIGGERS will work (with the addition of the ON agentmaster clause!), I recommend that you use ANSI JOIN syntax rather than the older JOIN syntax he posted. See

http://www.microsoft.com/sql/techinfo/tips/development/July23.asp

to find out why.

create trigger update_agentmaster
on agentmaster
for update
as
update db2.dbo.agentmaster set
name = i.name,
address = i.address,
tdsrate = i.tdsrate,
comm = i.comm
from db2.dbo.agentmaster a
Join db1.dbo.inserted i
On a.id = i.id
go

create trigger delete_agentmaster
on agentmaster
for delete
as
delete db2.dbo.agentmaster
from db2.dbo.agentmaster a
join deleted d
on a.ID=d.ID
go
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[JamesLean]

You don't need to qualify the inserted/deleted table names ie:

create trigger insert_agentmaster
on agentmaster
for insert as
insert into db2.dbo.agentmaster (id, name, address, tdsrate, comm)
select [id], [name], address, tdsrate, comm from inserted
go

 

[tlbroadbent]

Thanks James. I didn't catch that. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[osjohnm]

Rohan, as Terry pointed out you must drop the inserted and deleted tables you created. That should sort out the trigger execution. My apologies for leaving out the ON AGENTMASTER

Terry, thanks for pointing out my miss post with the ON AGENTMASTER . Thank you for converting my syntax to ANSI, by doing so I can now see how it is possible to join agentmaster on the deleted table. Using the old style of joining I just couldn't figure it out so thats why I used an IN. Thats a star for you!.

Terry, one last thing about ANSI sql, I have been discussing the change to ANSI with the head of development in our company and I have noticed that if you run a query using old style joins in QA it remains unchanged but running the same query in EM, SQL Server 2000 converts it to ANSI. How reliable is this conversion? Would it be sufficient to run all sql queries in EM to get the correct ANSI syntax and then move them back into the applications or is it better to re-write all the SQL Code?
I'll raise this in the ANSI SQL forum as well.

John

 

[tlbroadbent]

John,

I frankly don't like EM for query development so I've never observed the change you describe. I would guess the conversion is reliable. I have preferences in the way I code - identation, capitlization, order of tables, etc. so I would probably change the result from EM. However, the changes wouldn't likely change the way the query performed though it is possible.

If I had a lot of code to convert to ANSI standard syntax, I'd probably let EM do the bulk of the work. Then I'd test and perhaps tweak the code. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[RohanP]

Insert & Delete Triggers r working fine but the Update Trigger is giving an error

------------------------------
Invalid Object Name 'db2.dbo.inserted'
------------------------------

Because of this error previously i created inserted & deleted tables ;o), but now I have deleted both the tables I created..

But now again using ANSI standard syntax. I am getting same error.

This is the trigger I wrote -
create trigger Update_Agent
on Agent
for update
as
update db2.dbo.Agent set
agent_name = i.agent_name,
address = i.address,
city = i.city ,
pin = i.pin,
state = i.state,
country = i.country,
contactperson = i.contactperson,
cpemail = i.cpemail,
phone = i.phone,
fax = i.fax,
mobile = i.mobile,
email = i.email,
weburl = i.weburl,
ibv_refno_req = i.ibv_refno_req,
rateofcomm = i.rateofcomm,
color_code = i.color_code,
tds_rate = i.tds_rate
from db2.dbo.Agent a
Join db1.dbo.inserted i
On a.id = i.id
______________________________
- Regards Rohan (India,Mumbai)

 

[tlbroadbent]

Just use the name "inserted." You don't need to use "db1.dbo.inserted." See James comment earlier. I failed to remove them when I posted the code.

from db2.dbo.Agent a
Join inserted i
On a.id = i.id
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[RohanP]

Giving same error...
Invalid Object Name 'db1.dbo.inserted'
Even after I changed the Trigger as mentioned below...

create trigger update_agentmaster
on agentmaster
for update
as
update db2.dbo.agentmaster set
[name] = i.[name],
address = i.address,
tdsrate = i.tdsrate,
comm = i.comm
from db2.dbo.agentmaster a
Join inserted i
On a.[id] = i.[id]
go

Pls help.... ______________________________
- Regards Rohan (India,Mumbai)

 

[tlbroadbent]

You have to change the INSERT TRIGGER, also. Please look at all the TRIGGERs you created and remove the database and owner from the inserted and deleted tables. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[RohanP]

Thankzzz all u guys for ur help. ______________________________
- Regards Rohan (India,Mumbai)