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

LVL 1
Abdurahman AlmatrodiBusiness DevelopmentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
is there a foreign key between the two tables?
Abdurahman AlmatrodiBusiness DevelopmentAuthor Commented:
Yes there is. As I mentioned EmpID
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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?
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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).
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

chapmandewCommented:
Out of curiosity...why are you sorting on your INSERT...SELECT statement?
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...
chapmandewCommented:
ahhh....
Mark WillsTopic AdvisorCommented:
you mean to say I actually taught the grand almighty giant of an expert a new trick ?  :o)
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...
chapmandewCommented:
I learn new stuff all the time...actually have only used BULK INSERT a handful of times. ;)

you and all of your flippantness.  ;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.