Newbee: Trigger problem 
Author Message
 Newbee: Trigger problem

Hi all,
I'm new to SQL Server and am having problems with a trigger.  This is
what I want it to do.  Every time a new row is inserted in an employees
table, find out who that employees manager is, go to that empoyee (the
manager's) row and set the TYPE field to 'M' if it already isn't set.
The structure of the table is

Employee_ID    MGR_ID    TYPE
(sample data)

1                        3                E
3                        4                M
4                        5                M
5                        100            M
6                        4                E
7                        4                E
and so on..

Create Trigger my_trig on employees
FOR INSERT
AS


RETURN
/* for single row inserts only  will have to have code to handle
muti-row

BEGIN
    Update employee
    Set employee.Type='M'
    From inserted
    Where employee.ID=inserted.MGR_ID And employee.Type='E'
END

I think the update statement is messed up but I can't tell what it is.
I'm extremely frustrated and will appreciate any help.

Thanks

Asif Bashir



Tue, 28 Aug 2001 03:00:00 GMT
 Newbee: Trigger problem

well, for one, you don't need the
"from inserted" line.

for two, why do this for only a single
record-- why not join on the inserted
and get all of them in one shot?

Jason W. Paul
Adaptive InfoSystems

Quote:

>Hi all,
>I'm new to SQL Server and am having problems with a trigger.  This is
>what I want it to do.  Every time a new row is inserted in an employees
>table, find out who that employees manager is, go to that empoyee (the
>manager's) row and set the TYPE field to 'M' if it already isn't set.
>The structure of the table is

>Employee_ID    MGR_ID    TYPE
>(sample data)

>1                        3                E
>3                        4                M
>4                        5                M
>5                        100            M
>6                        4                E
>7                        4                E
>and so on..


>Create Trigger my_trig on employees
>FOR INSERT
>AS


>RETURN
>/* for single row inserts only  will have to have code to handle
>muti-row

>BEGIN
>    Update employee
>    Set employee.Type='M'
>    From inserted
>    Where employee.ID=inserted.MGR_ID And employee.Type='E'
>END

>I think the update statement is messed up but I can't tell what it is.
>I'm extremely frustrated and will appreciate any help.

>Thanks

>Asif Bashir



Wed, 29 Aug 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. newbee sp_spaceused problem

2. Newbee: Problem when installing SMS 1.2 on SQL 7

3. Newbee problem

4. NewBee's Network Connection Problem using VB

5. Newbee Question: Problem with mSQL driver

6. Trigger Problem (Delete Trigger)

7. Linked-Server (Trigger in a Trigger Problem)

8. Triggers, Triggers and more Triggers

9. newbee seeks upgrade help

10. sql 7 newbee

11. Newbee Quetion :-)

12. Newbee: How to connect through perl


 
Powered by phpBB® Forum Software