• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8625
  • Last Modified:

How to write a SQL Insert Trigger

Hi

I did not create any SQL trigger or procedure before. What I need is that I three tables as:
1- Parent [Employee]: EmpID (PK), EmpName ...etc.
2- Child [EmpCourse]: EmpID (FK), CourseName, ...etc
3- Child [EmpExperience]: EmpID (FK), ExperienceSource, ...etc.

I need when Insert a new record im Employee at same time insert a new record using EmpID in the two childs.

Please help.

Thanks

0
Abdurahman Almatrodi
Asked:
Abdurahman Almatrodi
  • 6
  • 5
  • 2
1 Solution
 
chapmandewCommented:
is there a foreign key between the two tables?
0
 
Abdurahman AlmatrodiBusiness DevelopmentAuthor Commented:
Yes there is. As I mentioned EmpID
0
 
chapmandewCommented:
OK..just making sure.  So, the deal is that the type of trigger you're talking about is an AFTER trigger...meaning that the FK is evaluated before the trigger is ran.  So, you're still going to get a violation of referential integrity before the trigger is ran.  

Modify your code to insert into the Parent able first, and then the child tables.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Abdurahman AlmatrodiBusiness DevelopmentAuthor Commented:
I need it to ne directly from databse itself. When user save the new Paren EmpID directly insert the same EmpID in other two tables using trigger after insert in Parent table. But how to write it?
0
 
chapmandewCommented:
OK..so you're inserting into the parent first....good deal.


create trigger mytriggername on parenttablename
after insert
as
begin

insert into childtable1(empid, otherfields...)
select empid, otherfields...
from inserted

insert into childtable2(empid, otherfields...)
select empid, otherfields...
from inserted

end
0
 
Mark WillsTopic AdvisorCommented:
well an insert trigger is easy enough, but you would normally create the employee, returning the ID, then insert the child records. If all on a single form for a user, then could also do in a SP. But the employee record would have to be added first... The other "gotcha" is that any column that cannot be NULL will need a "default" value of some kind.

So the trigger would look something like :

CREATE TRIGGER TRG_EMPLOYEE ON EMPLOYEE_test
FOR INSERT AS

INSERT EMPCOURSE (EMPID)
SELECT EMPID from INSERTED order by EMPID asc;

INSERT EMPEXPERIENCE (EMPID)
SELECT EMPID from INSERTED  order by EMPID asc;

GO



the order by is important if doing a bulk insert - otherwise it ends up in reverse sequence (assuming and ID field also exists on child tables).
0
 
Mark WillsTopic AdvisorCommented:
last time I looked, there was only the question about "is there a foreign key" slow typing, cup of coffee and post then whammo, lots of dialogue !

and to show why mine took longer...


create table employee_test (empid int identity,name varchar(20))
create table empcourse (id int identity, empid int, course varchar(20))
create table empexperience (id int identity, empid int, experience varchar(20))
GO
 
CREATE TRIGGER TRG_EMPLOYEE ON EMPLOYEE_test
FOR INSERT AS
 
INSERT EMPCOURSE (EMPID)
SELECT EMPID from INSERTED order by EMPID asc;
 
INSERT EMPEXPERIENCE (EMPID)
SELECT EMPID from INSERTED  order by EMPID asc;
 
GO
 
 
insert employee_test (name) 
select 'mark_11' as m2 union
select 'mark_12' as m2 union
select 'mark_13' as m2 
 
select * from empcourse
select * from empexperience
select * from employee_test

Open in new window

0
 
chapmandewCommented:
Out of curiosity...why are you sorting on your INSERT...SELECT statement?
0
 
Mark WillsTopic AdvisorCommented:
yep, try the bulk insert - ie when there are multiple rows in the inserted table.

in the example above, if I do the insert without the order by, then I get

employee_test = 1,mark_11  2,mark_12  3,mark_13
but...
empcourse = 1,mark_13  2,mark_12  3,mark_11

one of those nuances from the inserted table, and not that it really matters, just prefer to see the same chronology implied by the ID, becomes a bit more important if doing bulk work...
0
 
chapmandewCommented:
ahhh....
0
 
Mark WillsTopic AdvisorCommented:
you mean to say I actually taught the grand almighty giant of an expert a new trick ?  :o)
0
 
Mark WillsTopic AdvisorCommented:
gosh, that linkedin recommendation must be true. And maybe I should also ask a moderator to remove these couple of entries... I do apologise to all for being so flippant...
0
 
chapmandewCommented:
I learn new stuff all the time...actually have only used BULK INSERT a handful of times. ;)

you and all of your flippantness.  ;)
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now