Link to home
Create AccountLog in
Avatar of Jim Metcalf
Jim MetcalfFlag for United States of America

asked on

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.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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...
Avatar of Jim Metcalf

ASKER

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)
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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Guy!