[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trigger question

Posted on 2016-07-21
5
Medium Priority
?
73 Views
Last Modified: 2016-07-21
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.
0
Comment
Question by:jamesmetcalf74
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41723136
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
 

Author Comment

by:jamesmetcalf74
ID: 41723250
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41723259
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 41723260
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
 

Author Closing Comment

by:jamesmetcalf74
ID: 41723364
Thanks Guy!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question