Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how do i insert into tables with relationships from trigger

Posted on 2009-04-22
10
Medium Priority
?
148 Views
Last Modified: 2012-05-06
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)



declare @jobnumber as int
select @jobnumber = (select jobID from inserted)
insert into workorders
values (@jobnumber)

Open in new window

0
Comment
Question by:Dagome
  • 6
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24206574
just use insertd
insert into workorders (WorkOrderID)
 select jobid from inserted

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24206578
resp:
insert into workorders (jobid, WorkOrderID)
 select jobid, workorderid
  from inserted

Open in new window

0
 

Author Comment

by:Dagome
ID: 24206711
"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 :)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24206851
the INSERTED table is only available inside the trigger, and contains only the records inserted by the statement that raised the trigger.
0
 

Author Comment

by:Dagome
ID: 24207018
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 ?
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)

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24207081
I would code that like this:
insert into workorders 
 ( jobID )
select jobID 
  from inserted
 
insert into operations 
 ( workorderID )
select workorderID
  from workorders
 where jobID IN ( select jobID from inserted )

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24207098
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.
0
 

Author Closing Comment

by:Dagome
ID: 31573372
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 &.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24207187
>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
0
 

Author Comment

by:Dagome
ID: 24207311
i feel like i just took SQL Viagra :)
none of the online tutorials i've seen mentioned that trick :)
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question