Dagome
asked on
how do i insert into tables with relationships from trigger
every time a job is created I want to make a work order assigned to that job and few operations assigned to that work order.
I am writing an insert trigger on JOBS table that will create a record in WORK ORDERS and OPERATIONS tables.
JobID is a FK in WORK ORDERS table
WorkOrderID is FK in Operations.
I know how to grab the JobID in the trigger using "inserted" but how do i grab the WorkOrderID from the record i just created in workorders to make an Operation record?
This is the code so far (simplified)
I am writing an insert trigger on JOBS table that will create a record in WORK ORDERS and OPERATIONS tables.
JobID is a FK in WORK ORDERS table
WorkOrderID is FK in Operations.
I know how to grab the JobID in the trigger using "inserted" but how do i grab the WorkOrderID from the record i just created in workorders to make an Operation record?
This is the code so far (simplified)
declare @jobnumber as int
select @jobnumber = (select jobID from inserted)
insert into workorders
values (@jobnumber)
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
resp:
insert into workorders (jobid, WorkOrderID)
select jobid, workorderid
from inserted
ASKER
"inserted" changes everytime you make a insert into statment?
so the moment i inserted into Work Order table i lost the initial "inserted" from Job table ?
p.s. Hi Angellll :) thanks for you efforts i see your name a lot, as i use this site more than im willing to admit :)
so the moment i inserted into Work Order table i lost the initial "inserted" from Job table ?
p.s. Hi Angellll :) thanks for you efforts i see your name a lot, as i use this site more than im willing to admit :)
the INSERTED table is only available inside the trigger, and contains only the records inserted by the statement that raised the trigger.
ASKER
ok, now im confused :)
i guess i failed to mention that workorderID is an autonumber. i do not know its value until i create that record in workorders table. i guess the question is how i retrive that value from inside the trigger.
hmmmm
will this code work ?
i guess i failed to mention that workorderID is an autonumber. i do not know its value until i create that record in workorders table. i guess the question is how i retrive that value from inside the trigger.
hmmmm
will this code work ?
declare @jobnumber as int
select @jobnumber = (select jobID from inserted)
insert into workorders (jobID)
values (@jobnumber)
select @workorder = (select (workorderID)
from workorders
where jobID = @jobnumber)
insert into operations (workorderID)
values (@workorder)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to explain why I would not use your code:
your rely on the fact that only 1 row is inserted at a time into the table with the trigger, and will fail with something like:
subquery returned more than 1 row ...
my code will handle that correctly.
your rely on the fact that only 1 row is inserted at a time into the table with the trigger, and will fail with something like:
subquery returned more than 1 row ...
my code will handle that correctly.
ASKER
ok ok now i see
what was getting me confused is that you use SELECT statement "inside" the INSTERT INTO statement. this is first time i see that done.
Thank you.
Still so much to learn &.
what was getting me confused is that you use SELECT statement "inside" the INSTERT INTO statement. this is first time i see that done.
Thank you.
Still so much to learn &.
>what was getting me confused is that you use SELECT statement "inside" the INSTERT INTO statement.
that is one of the powers of sql language. if you did not know before, you missed that really!
glad to be of help
that is one of the powers of sql language. if you did not know before, you missed that really!
glad to be of help
ASKER
i feel like i just took SQL Viagra :)
none of the online tutorials i've seen mentioned that trick :)
none of the online tutorials i've seen mentioned that trick :)
Open in new window