Hi All,
The trigger in the following code is working on only last record
(sorry row) inserted . I wish to process "EACH ROW" in trigger as in
Oracle but without using cursor.
create table employee
(
EmpId int,
FirstName varchar(20),
LastName varchar(20),
Supervisor int
)
create table TreeEmployee
(
EmpId int,
FirstName varchar(20),
LastName varchar(20),
Supervisor int
)
GO
create trigger trgInsertOn on TreeEmployee for insert
as
BEGIN
DECLARE @.EMPID INT
DECLARE @.SUPERVISOR INT
SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
IF @.EMPID IS NOT NULL
BEGIN
INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
@.EMPID
END
END
GO
insert into employee
select 1,'Carl','Hogans',12
union
select 12,'Fred','Smith',NULL
union
select 16,'Sue','Bankers',1
union
select 26,'Frank','Green',12
union
select 55,'Karen','Feeders',NULL
union
select 56,'James','Black',12
union
select 57,'Kirk','Simmons',56
union
select 58,'Cliff','Page', 56
union
select 59,'Jimmy','Plant',56
union
select 60,'Jack','Cale', 59
union
select 61,'Robert','Santana',NULL
union
select 62,'Jack','Russell',1
INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
DROP TRIGGER TRGINSERTON
Drop table TreeEmployee
DROP table employee
With warm regards
Jatinder SinghINSERT INTO TreeEmployee
SELECT * FROM employee WHERE SUPERVISOR IN
(
Select EmpId from Inserted where Empid IS NOT NULL
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"jsfromynr" wrote:
> Hi All,
> The trigger in the following code is working on only last record
> (sorry row) inserted . I wish to process "EACH ROW" in trigger as in
> Oracle but without using cursor.
> create table employee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> create table TreeEmployee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> GO
> create trigger trgInsertOn on TreeEmployee for insert
> as
> BEGIN
> DECLARE @.EMPID INT
> DECLARE @.SUPERVISOR INT
> SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
> SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
> IF @.EMPID IS NOT NULL
> BEGIN
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
> @.EMPID
> END
> END
> GO
> insert into employee
> select 1,'Carl','Hogans',12
> union
> select 12,'Fred','Smith',NULL
> union
> select 16,'Sue','Bankers',1
> union
> select 26,'Frank','Green',12
> union
> select 55,'Karen','Feeders',NULL
> union
> select 56,'James','Black',12
> union
> select 57,'Kirk','Simmons',56
> union
> select 58,'Cliff','Page', 56
> union
> select 59,'Jimmy','Plant',56
> union
> select 60,'Jack','Cale', 59
> union
> select 61,'Robert','Santana',NULL
> union
> select 62,'Jack','Russell',1
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
> SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
> DROP TRIGGER TRGINSERTON
> Drop table TreeEmployee
> DROP table employee
> With warm regards
> Jatinder Singh
>|||What exactly are you trying to do? Maybe you should first read a book or two
an trees and hierarchies.
When trying to build trees with triggers you need to consider the fact that
SQL prevents execution of recursive triggers, and consider the fact that the
maximum nesting level for recursions in SQL 2000 is 32.
ML|||Hi ML,
Yes , you are right .
I had read the articles written by Joe Celko .
'IF @.EMPID IS NOT NULL ' will stop the insert if empid is null
and we can add if TRIGGER_NESTLEVEL(object_id('YourTrigger
Name')) =32
return . Can't we? I am looking for an alternative approach .
With warm regards
Jatinder Singh|||First it's imperative that you design a good tree model on paper. One of the
most important requirements of any data tree IMHO is to prevent circular
referencing.
In your case there are two entities you need to design appropriate storage
for:
1) employees - selecting a unique key for each employee (a primary key,
maybe) is imperative; and
2) employee_hierarchy - not only do employees supervise other employees and
answer to another employee, relationships may also be contextual (roles,
projects) - in this case hierarchy instances must be supported.
Think about that. Entity #2 is a data tree which references employees, and
is also a self-referenced entity.
Look at this example in my blog:
http://milambda.blogspot.com/2005/0...or-monkeys.html
You can use the function to create your own 'hiearchy-discovery' methods.
ML|||Hi ML,
Really Your blog is excellent!! What I can say about that ? I
read it earlier .
With warm regards
Jatinder Singh|||:)
I'm glad you find it useful.
ML|||I might be missing something here, but here it goes...
When you write a trigger it only fires once per 'triggering' event. So in
your trigger below, only one insert is being run, only one trigger will run.
Where are all my records? Triggers expost an 'inserted' table. Inside this
special table are all of the records that were inserted. There is also a
table called deleted, but for updates triggers use deleted and inserted
(this is a seperate topic).
So onto the meat and potatos, you need to treat the inserted table as a
table! It has multiple rows. So you have two options as I see it.
1) Use a cursor to iterate over each row in the instered table (yuk)
2) Rewrite your trigger to support multiple records.
Here's my stab at #2:
CREATE TRIGGER trgInsertOn ON employee
FOR INSERT
AS
BEGIN
INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
SELECT EmpId, FirstName, LastName, Supervisor
FROM inserted
WHERE supervisor IS NOT NULL
END
GO
Now, having done that I have a few remarks... You shouldn't be storing
anything in the supervisor table other than the EmpID and SupID. They
should both be FK's back to the employee table. Also, the trigger needs to
be on the insert of the employee table NOT the TreeEmployee table.
I'm really not sure what your ultimate goal is here. Your TreeEmployee
simply replicates your employee table except where EmpId is null...
That should get you going!
HTH,
Ben
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123066839.814729.101130@.g49g2000cwa.googlegroups.com...
> Hi All,
> The trigger in the following code is working on only last record
> (sorry row) inserted . I wish to process "EACH ROW" in trigger as in
> Oracle but without using cursor.
> create table employee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> create table TreeEmployee
> (
> EmpId int,
> FirstName varchar(20),
> LastName varchar(20),
> Supervisor int
> )
> GO
> create trigger trgInsertOn on TreeEmployee for insert
> as
> BEGIN
> DECLARE @.EMPID INT
> DECLARE @.SUPERVISOR INT
> SELECT @.EMPID = EMPID ,@.SUPERVISOR = SUPERVISOR FROM INSERTED
> SELECT 'INSERTINTG',@.EMPID , @.SUPERVISOR
> IF @.EMPID IS NOT NULL
> BEGIN
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE SUPERVISOR =
> @.EMPID
> END
> END
> GO
> insert into employee
> select 1,'Carl','Hogans',12
> union
> select 12,'Fred','Smith',NULL
> union
> select 16,'Sue','Bankers',1
> union
> select 26,'Frank','Green',12
> union
> select 55,'Karen','Feeders',NULL
> union
> select 56,'James','Black',12
> union
> select 57,'Kirk','Simmons',56
> union
> select 58,'Cliff','Page', 56
> union
> select 59,'Jimmy','Plant',56
> union
> select 60,'Jack','Cale', 59
> union
> select 61,'Robert','Santana',NULL
> union
> select 62,'Jack','Russell',1
> INSERT INTO TreeEmployee SELECT * FROM employee WHERE EMPID = 12
> SELECT * FROM TreeEmployee ORDER BY EMPID, Supervisor
> DROP TRIGGER TRGINSERTON
> Drop table TreeEmployee
> DROP table employee
> With warm regards
> Jatinder Singh
>|||Ok I think I see my mistake (maybe...) Here is my corrected trigger... If
this works, I'd tweak it to handle updates and deletes as well...
CREATE TRIGGER trgInsertOn ON employee
FOR INSERT
AS
BEGIN
-- start transaction to encapsulate the delete / insert
BEGIN TRANSACTION
-- to prevent dups, clear out inserted supervisors records from the tree
-- this also catches stale records
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT Supervisor
FROM inserted
)
-- add anyone in the employee table to the TreeEmployee table
-- that has a newly inserted EmpId as a supervisor on them
INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
SELECT EmpId, FirstName, LastName, Supervisor
FROM employee
WHERE supervisor IN
(
SELECT EmpId
FROM inserted
)
COMMIT TRANSACTION
END
GO
"Ben" <ben@.online.nospam> wrote in message
news:c060b$42f12956$d8445835$10658@.FUSE.NET...
>I might be missing something here, but here it goes...
> When you write a trigger it only fires once per 'triggering' event. So in
> your trigger below, only one insert is being run, only one trigger will
> run.
> Where are all my records? Triggers expost an 'inserted' table. Inside
> this special table are all of the records that were inserted. There is
> also a table called deleted, but for updates triggers use deleted and
> inserted (this is a seperate topic).
> So onto the meat and potatos, you need to treat the inserted table as a
> table! It has multiple rows. So you have two options as I see it.
> 1) Use a cursor to iterate over each row in the instered table (yuk)
> 2) Rewrite your trigger to support multiple records.
> Here's my stab at #2:
> CREATE TRIGGER trgInsertOn ON employee
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
> SELECT EmpId, FirstName, LastName, Supervisor
> FROM inserted
> WHERE supervisor IS NOT NULL
> END
> GO
> Now, having done that I have a few remarks... You shouldn't be storing
> anything in the supervisor table other than the EmpID and SupID. They
> should both be FK's back to the employee table. Also, the trigger needs
> to be on the insert of the employee table NOT the TreeEmployee table.
> I'm really not sure what your ultimate goal is here. Your TreeEmployee
> simply replicates your employee table except where EmpId is null...
> That should get you going!
> HTH,
> Ben
>
>
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1123066839.814729.101130@.g49g2000cwa.googlegroups.com...
>|||:( where does it end...
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT Supervisor
FROM inserted
)
Needs to be
DELETE FROM TreeEmployee
WHERE Supervisor IN
(
SELECT EmpId
FROM inserted
)
"Ben" <ben@.online.nospam> wrote in message
news:9cfb4$42f12d04$d8445835$11906@.FUSE.NET...
> Ok I think I see my mistake (maybe...) Here is my corrected trigger...
> If this works, I'd tweak it to handle updates and deletes as well...
> CREATE TRIGGER trgInsertOn ON employee
> FOR INSERT
> AS
> BEGIN
> -- start transaction to encapsulate the delete / insert
> BEGIN TRANSACTION
> -- to prevent dups, clear out inserted supervisors records from the tree
> -- this also catches stale records
> DELETE FROM TreeEmployee
> WHERE Supervisor IN
> (
> SELECT Supervisor
> FROM inserted
> )
> -- add anyone in the employee table to the TreeEmployee table
> -- that has a newly inserted EmpId as a supervisor on them
> INSERT INTO TreeEmployee (EmpId, FirstName, LastName, Supervisor)
> SELECT EmpId, FirstName, LastName, Supervisor
> FROM employee
> WHERE supervisor IN
> (
> SELECT EmpId
> FROM inserted
> )
> COMMIT TRANSACTION
> END
> GO
>
> "Ben" <ben@.online.nospam> wrote in message
> news:c060b$42f12956$d8445835$10658@.FUSE.NET...
>
