Trigger Problem - 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 2005 Forums

SQL Server Administration (2005)

Trigger Problem

Author

Topic

mpolaiah
Starting Member

24 Posts

Posted - 2009-07-31 : 15:51:21

Hello,

Does anyone know how to get a trigger to fire while you are uploading multiple records into a SQL Server 2005 Express DB using the DTS Wizard?

On insert of a person's record I want the trigger to find their id# from another table and update their information accordingly.

I have the trigger built, but it will not fire when I use the DTS Wizard.

my trigger is..........................
CREATE TRIGGER TRIG_AH_PRE_REQUEST_DETAILS
ON AH_PRE_REQUEST_DETAILS
after insert
AS
BEGIN
declare @pin_nRequestID int,@nSMSCount int
select @pin_nRequestID= nRequestID from inserted

select @nSMSCount= total from(
select count(*) as total from AH_PRE_REQUEST_DETAILS
where nRequestID=@pin_nRequestID
) a

update AH_PRE_REQUEST set nSMSCount=@nSMSCount
where nRequestID=@pin_nRequestID

END

here first on record inserted that is detais record in the table AH_PRE_REQUEST
that record is P.K
then based on the record
bulk inserted int table AH_PRE_REQUEST_DETAILS some 50
then automatically update the
main table count=50
based on the P.K

how to write the trigger

plz help meee..................................

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-31 : 17:06:53

You can't assign variables based on INSERTED table in triggers because triggers can insert many rows. You will only get 1 value instead of all values. try this:


       update apr set 
              nSMSCount=d.total
       from   AH_PRE_REQUEST apr
       inner join (                     
              select i.nRequestID
                     ,count(*) total
              from   inserted i
              join   AH_PRE_REQUEST_DETAILS aprd 
                            on aprd.nRequestID = i.nRequestID
              group by i.nRequestID
              ) d
                  on d.nRequestID = apr.nRequestID

Be One with the Optimizer
TG

EDIT:
forgot the ON criteria

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources