Problems with a trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems with a trigger

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-18 : 14:43:34

This insert statement

insert into Strat_unit_action
select 'insert',txt.strat_unit,txt.strat_name_set
from strat_Unit_dts dts right join strat_unit_txt txt on txt.strat_name_set=dts.strat_name_set and txt.strat_unit=dts.strat_unit
where dts.strat_unit is null or txt.strat_unit is null
go

inserts values into a tracking table. About 80 values at a time are being passed to it with this statement (don't worry.. it fine, just posting it so ya got all the detail). Theres another one simular to this except the values it send say delete.

the trigger on the table strat_unit_action is

CREATE TRIGGER Strat_unit_action_trig
ON Strat_unit_action
FOR INSERT
AS
BEGIN
if (select action_name from inserted)= 'insert' insert into Strat_unit_dts select * from strat_unit_txt where Strat_Name_Set = (select Strat_Name_Set from inserted) and Strat_Unit =(select Strat_Unit from inserted)
end

Now, this will work if only one value at a time is inserted. However the inserted table thats being selected from has 80 entries in it some times. I can use a cursor (I'm writing it as I wait for a response) but I was wondering if there was a better method (actually.. I know there is... I just don't know it myself

)

I've spent a little time forum browsing and read the trigger articles, but I wasn't able to find much... Any suggestions would be apreciated

-----------------------
Take my advice, I dare ya

izaltsman
A custom title

1139 Posts

Posted - 2002-06-18 : 15:00:07

If you replace your '=' with IN operators the trigger will work...


CREATE TRIGGER Strat_unit_action_trig 
ON Strat_unit_action 
FOR INSERT 
AS 
BEGIN 
if (select action_name from inserted)= 'insert' 
BEGIN
    insert into Strat_unit_dts 
    select * from strat_unit_txt 
    where Strat_Name_Set IN (select Strat_Name_Set from inserted) 
            and Strat_Unit IN (select Strat_Unit from inserted) 
END
end

You could actually make it a bit more elegant by re-writing it with a single EXISTS clause instead of 2 IN's... In fact, you probably should do that, 'cause in case there are multiple records in the inserted table, the above might return unexpected results.

Edited by - izaltsman on 06/18/2002 15:01:53

DGMelkin
Starting Member

24 Posts

Posted - 2002-06-18 : 15:09:33

Would this work for you:

 
CREATE TRIGGER Strat_unit_action_trig 
ON Strat_unit_action 
FOR INSERT 
AS 
BEGIN 
INSERT INTO Strat_unit_dts 
SELECT txt.* 
FROM inserted ins 
JOIN strat_unit_txt txt ON txt.Strat_Name_Set = ins.Strat_Name_Set AND txt.Strat_Unit = ins.Strat_Unit 
WHERE ins.action_name = 'insert'
END

izaltsman
A custom title

1139 Posts

Posted - 2002-06-18 : 15:09:41

The EXISTS solution would look something like this:


CREATE TRIGGER Strat_unit_action_trig 
ON Strat_unit_action 
FOR INSERT 
AS 
BEGIN 
if (select action_name from inserted)= 'insert' 
BEGIN
    insert into Strat_unit_dts 
    select * from strat_unit_txt outer_tbl
    where EXISTS (select 1 from inserted inner_tbl where inner_tbl.strat_name_set = outer_tbl.strat_name_set 
                                                   AND inner_tbl.strat_unit = outer_tbl.strat_unit ) 
END
end

Sniped!

Edited by - izaltsman on 06/18/2002 15:11:28

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-18 : 15:19:45

Never even considered a join on the inserted table... thanks guys

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources