Cascaded inserts and updates? - 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

Cascaded inserts and updates?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-28 : 09:29:19

Hi

I have 3 table (tbl1, tbl2, tbl3).

tbl1 colums ---Pid(autonNumber),Pcode(unique values),PGuid

tbl2 columns---Pid, CatId,Fid

tbl3 Colums----Pid,BundleId

Using Trigger(After Insert) in tbl1 to insert the data of tbl1.Pid to tbl2.Pid and tbl3.Pid . tbl1 will be uploaded with new rows and existing rows will be updated from the csv file.

Trigger in tbl1 works fine by inserting data to tbl2.Pid and tbl3.Pid.

My questions are :
1. Is it possible to use trigger to update tbl2 and tbl3 if records exist if not insert new record to colum Pid in tbl2 and tbl3 from Pid of tbl1

2.Or is it possible to use sql procedure to do cascaded updates or inserts by merging datasets from tables and csv file using asp.net

3.Or How can I use single Trigger to insert if not exist and update if data exist.

4.Or Do I have to use two trigger one for insert and one for update. If this is the case how do check for data existance.

After insert Triger I am using at the moment is below (replied as an answer in this forum for my previous thread)

create trigger t1 on tbl1
after insert
as
begin
declare @id int
select @id=pid from inserted
insert into tbl2(pid) values(@id)
insert into tbl3(pid) values(@id)
end

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 11:31:21

without having a full understanding of your system, it seems like you should be using a transaction to include all the inserts, rather than insert into a table and use triggers to populate subsequent tables. this is really primative pseudo code, but here to make the point...

create proc load @pid int
as
begin tran
insert tbl1 select @pid
insert tbl2 select @pid
insert tbl3 select @pid
if not exists (select 0 from tbl2 where pid = @pid) or not exists (select 0 from tbl3 where pid = @pid)
begin
--failure
rollback
end
else
begin
--success
commit tran
end

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-28 : 12:35:21

Hi

Thanks for the reply.

This is for an e-commerce website. Trying to do bulk upload from csv. So I have few tables products, products_category,ProductImage,ProductDescription etc. The whole idea is insert or update the data to all related table in one button click. I managed to upload to one table from csv then using triggers to insert or update other relavant tables.

I have sorted out by creating two triggers one for insert and one for update and tested, it works as below.

tblMain - will be inserted and updated from csv,
------it has trigger1(insert to tbl1) and trigger2(update to tbl1)

tbl1(products)- has trigger1(insert to tbl2) and trigger2(update to tbl2)

tbl2 (category)

IN SHORT I AM USING TRIGGERS TO DO CASCADING UPDATE and INSERTS

IS it effiecient or some other way to achive this ?
---------------------OR-------------------------------------

Is your code in your reply will do the above said ?. Sorry for hassle I am not good at sql

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 13:11:25

sorry i should have paid attention to the post title. if you are using foreign keys you can simply turn on cascading updates and deletes look in books online for your sql verision "Cascading Referential Integrity Constraints"

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-29 : 02:00:25

Hi!

Using on update cascade, you change value on the parent table then the corresponding changes will be reflect on the child table also.(same as on update cascade).

This is the exmple of using on update cascade in sql.

create table Newparent(id int not null primary key,name varchar(20))
create table Newchild(id int not null,price int)
alter table newchild add constraint fk_id foreign key (id) references newparent(id) on update cascade
alter table newparent add no1 int identity
select * from Newparent
select * from newchild

insert Newparent values(1,'a')
insert Newparent values(2,'a')
insert Newparent values(3,'a')
insert Newparent values(4,'a')

insert newchild values(1,100)
insert newchild values(1,150)
insert newchild values(2,200)
insert newchild values(2,300)

update newparent set id=22 where no1=2

select * from newparent
select * from newchild

I hope that this will help you a bit.

Kiruthika
http://www.ictend.eu

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-29 : 05:11:39

Thanks again Kiruthika. At the moment I am using trigger for insert and update from your reply for my other thread. It works well as I wanted. But I will give a try for this code as well. Thanks a lot

quote:
Originally posted by kiruthika

Hi!

Using on update cascade, you change value on the parent table then the corresponding changes will be reflect on the child table also.(same as on update cascade).

This is the exmple of using on update cascade in sql.

create table Newparent(id int not null primary key,name varchar(20))
create table Newchild(id int not null,price int)
alter table newchild add constraint fk_id foreign key (id) references newparent(id) on update cascade
alter table newparent add no1 int identity
select * from Newparent
select * from newchild

insert Newparent values(1,'a')
insert Newparent values(2,'a')
insert Newparent values(3,'a')
insert Newparent values(4,'a')

insert newchild values(1,100)
insert newchild values(1,150)
insert newchild values(2,200)
insert newchild values(2,300)

update newparent set id=22 where no1=2

select * from newparent
select * from newchild

I hope that this will help you a bit.

Kiruthika
http://www.ictend.eu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources