Trigger question

question about a trigger.  
i have a trigger that fires off when an insert takes place on a table and then it populates another table with the exact same contents plus one extra column.   I test it with ssms and it works fine..... only when i do one update statement at a time.  
when i right a group of 4 or 5 update statements, highlight them all and f5 them in the ssms interface, the trigger will only add the first update statement, but i do find all 5 inserts on the original target table......  
I might need to put more information in here but was hoping that someone might know what is probably going from the above description.
jamesmetcalf74Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this is the way it should be coded
no variables needed, as you can see.



create trigger [dbo].[trgafterInsert] on [dbo].[UBREAD]
for insert as

insert into [dbo].[UBREAD1]
(AcctNo, Service, ReadCC, ReadYY, SeqNo, ServiceId, MeterNo, ReadDate,
 FromDev, Reading, Demand, Mult, BillMm, DemandType, ADDDTTM, ADDBY)

select
 AcctNo,
Service,
ReadCC,
ReadYY,
SeqNo,
ServiceId,
MeterNo,
ReadDate,
FromDev,
Reading,
Demand,
Mult,
BillMm,
DemandType,
GETDATE(),
ADDBY
from inserted
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the trigger code is coded to expect 1 record being updated, like in oracle the "for each row" syntax

do this

create trigger...
as
insert into othertable
select ..., x from inserted -- or deleted depending if you want the new or old values...
0
 
jamesmetcalf74Author Commented:
Thanks Guy-
Can you look at my actual trigger code and focus me in on your suggestion..

create trigger [dbo].[trgafterInsert] on [dbo].[UBREAD]
for insert as

declare @AcctNo varchar(10);
declare @Service int;
declare @ReadCC int;
declare @ReadYY int;
declare @SeqNo int;
declare @ServiceId int;
declare @MeterNo varchar(10);
declare @ReadDate datetime;
declare @FromDev varchar(2);
declare @Reading bigint;
declare @Demand decimal(18,3);
declare @Mult decimal(18,3);
declare @BillMm int
declare @DemandType varchar(4);
declare @ADDDTTM datetime;
declare @ADDBY nvarchar(30);
declare @MODDTTM datetime;
declare @MODBY nvarchar(30);


select @AcctNo=i.AcctNo from inserted i;
select @Service=i.Service from inserted i;
select @ReadCC=i.ReadCC from inserted i;
select @ReadYY=i.ReadYY from inserted i;
select @SeqNo=i.SeqNo from inserted i;
select @ServiceId=i.ServiceId from inserted i;
select @MeterNo=i.MeterNo from inserted i;
select @ReadDate=i.ReadDate from inserted i;
select @FromDev=i.FromDev from inserted i;
select @Reading=i.Reading from inserted i;
select @Demand=i.Demand from inserted i;
select @Mult=i.Mult from inserted i;
select @BillMm=i.BillMm from inserted i;
select @DemandType=i.DemandType from inserted i;

insert into [dbo].[UBREAD1]
(AcctNo, Service, ReadCC, ReadYY, SeqNo, ServiceId, MeterNo, ReadDate,
 FromDev, Reading, Demand, Mult, BillMm, DemandType, ADDDTTM, ADDBY)

 VALUES
 (@AcctNo,
@Service,
@ReadCC,
@ReadYY,
@SeqNo,
@ServiceId,
@MeterNo,
@ReadDate,
@FromDev,
@Reading,
@Demand,
@Mult,
@BillMm,
@DemandType,
GETDATE(),
@ADDBY)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is the way it should be coded
no variables needed, as you can see.



create trigger [dbo].[trgafterInsert] on [dbo].[UBREAD]
for insert as

insert into [dbo].[UBREAD1]
(AcctNo, Service, ReadCC, ReadYY, SeqNo, ServiceId, MeterNo, ReadDate,
 FromDev, Reading, Demand, Mult, BillMm, DemandType, ADDDTTM, ADDBY)

select
 AcctNo,
Service,
ReadCC,
ReadYY,
SeqNo,
ServiceId,
MeterNo,
ReadDate,
FromDev,
Reading,
Demand,
Mult,
BillMm,
DemandType,
GETDATE(),
ADDBY
from inserted
0
 
jamesmetcalf74Author Commented:
Thanks Guy!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.