Solved

Trigger question

Posted on 2016-07-21
5
52 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 500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 28
Download ms sql express. 2 28
Getting invalid Syntax SQL. 3 21
Need to find substring in SQL 5 15
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

830 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