?
Solved

Trigger question

Posted on 2016-07-21
5
Medium Priority
?
67 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 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