Insert Trigger for Parent/Child

I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger.

Thanks,
James

Where did you put the insert logic to the child table? I mean did you insert 1~N row(s) into child table in the insert trigger of parent table? Anyways I suppose you did like I say, as it helps to matainence the data consistency. Then you can add a column to parent table, which is used to record effected rows in child by the row. And in the insert trigger of parent table, let's declare a INT variable with initial value 0, every time an insert to child table will cause the variable increased by 1. After all required rows have been inserted into child, update the row in parent table with the INT variable. Something like this:

create trigger trg_ins_Parent on tbl_Parent for insert
as
begin
declare @.i int
set @.i=0
while(...)
begin
insert into tbl_Child select val1,val2,...
set @.i=@.i+1
end
update tbl_Parent setChildCnt=@.i
where rowid=inserted.rowid
end
go

Insert Trigger

I am facing problem in creating a insert trigger for the following scenario.

i have transactions, control tables

whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.

control table has these fields (control_desc, control_value)

can some one help me to write a trigger (insert trigger) in transactions table.

Tanks for the Help

Coudl you please provide more information like DDL code, showing which column you want to update etc.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the replay

Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid

Fields in Control Table: id, id_value

I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.

Can u help me reating this trigger.

Regards

|||

Try this

--<Run Once>

drop table Transactions

drop table control

go

create table control

(

control_desc varchar(5) not null primary key,

control_value int not null

)

create table Transactions

(

trans_id int not null identity,

control_desc varchar(5) not null foreign key references control(control_desc),

control_value int not null

)

go

create trigger ti_Transactions on Transactions for insert

as

set nocount on

update c

set c.control_value = c.control_value + 1

from control c

join inserted i

on i.control_desc = c.control_desc

go

insert control select 'ABCDE', 10001

go

--</Run Once>

--<Repeatable>

insert transactions (control_desc, control_value)

select control_desc, control_value

from control

where control_desc = 'ABCDE'

go

select * from control

select * from transactions

--</Repeatable>

|||

The following query may help you...

Code Snippet

create table control (
id int,
id_value int)

Go

create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)

Go

Insert Into control values(1,0)--Initiating the value

Go

Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;

Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End

Go

Insert Into Transactions values(null, 1, 10,getdate(),newid())

GO

select * from Transactions
select * from control

Insert Trigger

I am facing problem in creating a insert trigger for the following scenario.

i have transactions, control tables

whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.

control table has these fields (control_desc, control_value)

can some one help me to write a trigger (insert trigger) in transactions table.

Tanks for the Help

Coudl you please provide more information like DDL code, showing which column you want to update etc.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the replay

Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid

Fields in Control Table: id, id_value

I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.

Can u help me reating this trigger.

Regards

|||

Try this

--<Run Once>

drop table Transactions

drop table control

go

create table control

(

control_desc varchar(5) not null primary key,

control_value int not null

)

create table Transactions

(

trans_id int not null identity,

control_desc varchar(5) not null foreign key references control(control_desc),

control_value int not null

)

go

create trigger ti_Transactions on Transactions for insert

as

set nocount on

update c

set c.control_value = c.control_value + 1

from control c

join inserted i

on i.control_desc = c.control_desc

go

insert control select 'ABCDE', 10001

go

--</Run Once>

--<Repeatable>

insert transactions (control_desc, control_value)

select control_desc, control_value

from control

where control_desc = 'ABCDE'

go

select * from control

select * from transactions

--</Repeatable>

|||

The following query may help you...

Code Snippet

create table control (
id int,
id_value int)

Go

create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)

Go

Insert Into control values(1,0)--Initiating the value

Go

Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;

Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End

Go

Insert Into Transactions values(null, 1, 10,getdate(),newid())

GO

select * from Transactions
select * from control

sql

Insert the result of extended stored procedure into a table

Hi,
Can you tell me how can i put the result of an extended
stored procedure into a table?
I've done this by creating the table and then inserting
the result as follows:
create table FreeVolumeSpace(
Drive char(1),
MB_Free int)
insert into FreeVolumeSpace exec master..xp_fixeddrives
but im curious to know how can i do it using the
select into statement.
Best regardsNot quite sure why you want to do it but you can use OPENROWSET to loopback
to your local server e.g.
select a.* into #space
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);',
'SET FMTONLY OFF exec master.dbo.xp_fixeddrives') AS a
go
select * from #space
go
drop table #space
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1bee001c45229$986a8130$a601280a@.phx
.gbl...
> Hi,
> Can you tell me how can i put the result of an extended
> stored procedure into a table?
> I've done this by creating the table and then inserting
> the result as follows:
> create table FreeVolumeSpace(
> Drive char(1),
> MB_Free int)
> insert into FreeVolumeSpace exec master..xp_fixeddrives
> but im curious to know how can i do it using the
> select into statement.
> Best regards

Insert the result of extended stored procedure into a table

Hi,
Can you tell me how can i put the result of an extended
stored procedure into a table?
I've done this by creating the table and then inserting
the result as follows:
create table FreeVolumeSpace(
Drive char(1),
MB_Free int)
insert into FreeVolumeSpace exec master..xp_fixeddrives
but im curious to know how can i do it using the
select into statement.
Best regards
Not quite sure why you want to do it but you can use OPENROWSET to loopback
to your local server e.g.
select a.* into #space
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);',
'SET FMTONLY OFF exec master.dbo.xp_fixeddrives') AS a
go
select * from #space
go
drop table #space
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1bee001c45229$986a8130$a601280a@.phx.gbl...
> Hi,
> Can you tell me how can i put the result of an extended
> stored procedure into a table?
> I've done this by creating the table and then inserting
> the result as follows:
> create table FreeVolumeSpace(
> Drive char(1),
> MB_Free int)
> insert into FreeVolumeSpace exec master..xp_fixeddrives
> but im curious to know how can i do it using the
> select into statement.
> Best regards

Insert the result of extended stored procedure into a table

Hi,
Can you tell me how can i put the result of an extended
stored procedure into a table?
I've done this by creating the table and then inserting
the result as follows:
create table FreeVolumeSpace(
Drive char(1),
MB_Free int)
insert into FreeVolumeSpace exec master..xp_fixeddrives
but im curious to know how can i do it using the
select into statement.
Best regardsNot quite sure why you want to do it but you can use OPENROWSET to loopback
to your local server e.g.
select a.* into #space
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);',
'SET FMTONLY OFF exec master.dbo.xp_fixeddrives') AS a
go
select * from #space
go
drop table #space
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1bee001c45229$986a8130$a601280a@.phx.gbl...
> Hi,
> Can you tell me how can i put the result of an extended
> stored procedure into a table?
> I've done this by creating the table and then inserting
> the result as follows:
> create table FreeVolumeSpace(
> Drive char(1),
> MB_Free int)
> insert into FreeVolumeSpace exec master..xp_fixeddrives
> but im curious to know how can i do it using the
> select into statement.
> Best regards