Trigger help: instead of trigger multiple row updates

Question

Trigger help: instead of trigger multiple row updates

Robert Craig

Old Hand

Points: 325
More actions
September 14, 2005 at 5:36 am

#91186

I have just written an instead of insert, update trigger. I presumed the trigger would fire for each individual insert or update. As it is my update statement affects numerous rows and my trigger only seems to do the calculations on one of them. I therefore am guessing the instead of trigger fires only once and it is upto me to some how scroll through the inserted table and perform the calculations for each row. If this is the case how do I go about it? if not any suggestions? I thought I might have needed a for trigger but one of my columns is my table is ntext and it will not work.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 1

Can you post the actual trigger as that will help the solution?

If many rows are updated/inserted, the trigger will be called once with all the affected rows in the inserted and deleted tables as appropriate.

Robert Craig Old Hand Points: 325 More actions · Answer 2

CREATE TRIGGER trigOrderDetail ON [dbo].[Order Detail]

instead of insert,update

AS

declare @ProdWeekend datetime,

@Day varchar(20),

@ProdWeek datetime,

@ProdDay varchar(20),

@prog datetime,

@ProgDay varchar(20),

@CalWeekend datetime,

@CalDay varchar(20),

@Cal datetime,

@PresAssemWeekend datetime,

@AssemDay varchar(20),

@PresAssem datetime,

@StockWeekend datetime,

@StockDay varchar(20),

@Stock datetime

declare @OrderID int ,

@DueDate datetime,

@plus31b int,

@plusDeg int,

@RescheduleDate int,

@plusCal int,

@plusPresAssem int,

@plusStock int

select @OrderID = [Order ID],

@DueDate = [Due Date],

@plus31b = [+31b],

@plusDeg = [+Deg],

@RescheduleDate = [Reschedule Date],

@plusCal = [+Cal],

@plusPresAssem = [+PresAssem],

@plusStock = [+Stock]

from inserted

select @Prodweekend = @duedate - @plus31b - @plusdeg - 1

select @Day = dbo.fn_formatdate(@prodweekend, 'dddd')

select @Prodweek = case @day when 'Saturday' then @prodweekend - 1

when 'Sunday' then @prodweekend - 2

else @prodweekend end

select @ProdDay = dbo.fn_formatDate(@prodweek, 'dddd')

select @prog = case @prodday when 'Sunday' then @prodweek - 2

when 'Monday' then @prodweek -3

else @prodweek - 1 end

select @progday = dbo.fn_formatDate(@prog, 'dddd')

select @CalWeekend = @ProdWeek - 1 - @plusCal

select @CalDay = dbo.fn_formatDate(@Calweekend, 'dddd')

select @Cal = case @CalDay when 'Saturday' then @CalWeekend - 2

when 'Sunday' then @Calweekend - 2

else @Calweekend - 1 end

select @PresAssemWeekend = @Cal - @PlusPresAssem

select @AssemDay = dbo.fn_formatDate(@PresAssemWeekend, 'dddd')

select @PresAssem = case @AssemDay when 'Saturday' then @PresAssemWeekend - 1

when 'Sunday' then @PresAssemWeekend - 2

else @PresAssemWeekend end

select @StockWeekend = @PresAssem - @plusStock

select @StockDay = dbo.fn_formatDate(@StockWeekend, 'dddd')

select @Stock = case @StockDay when 'Saturday' then @StockWeekend - 1

when 'Sunday' then @StockWeekend - 2

else @StockWeekend end

insert into [order detail] ([SAP ORDER NUMBER] , [Flowconditioner ID], [Item Number], [SAP Date], [Order Receipt Date], [Amendment Receipt Date],

[Due Date], [Amendment Number], [Amendment Notes], [Meter Part Number], [AMT Part Number], [AMT Serial Number],

[Flowcon Part Number],[AZT570 Part Number],[Mounting Boss Part Number] ,[Prefix To Serial No] ,[Gas Ref] ,[Sensor Range] ,[20mA Value],

[Flow Ref], [Pulse Value],[Pressure Ref], [Operating Pressure], [Operating Temperature], [Temp Ref], [Insertion Line Size], [Insertion Line Size 2] ,

[Remarks],[Order Detail Notes] ,[Tag Number],[Maximum Air Flow] ,[20 mA Air Flow] ,[Order Entered By],[Extrapolation] ,[Installation Factor],[Default Value Used] ,

[Mixed Gas Name] ,[Normal Density] ,[Standard Density] ,[Production Notes] ,[Flowcell Status],[Head/Ins Status] ,[Reschedule Date],[+31b],[+Deg] ,[+Stock],[+PresAssem] ,

[+Cal],[Cal Date],[UK Customer ?],[Revised Delivery],[On Hold],[Hold Comments])

select [SAP ORDER NUMBER] , [Flowconditioner ID], [Item Number], [SAP Date], [Order Receipt Date], [Amendment Receipt Date],

[Due Date], [Amendment Number], [Amendment Notes], [Meter Part Number], [AMT Part Number], [AMT Serial Number],

[Flowcon Part Number],[AZT570 Part Number],[Mounting Boss Part Number] ,[Prefix To Serial No] ,[Gas Ref] ,[Sensor Range] ,[20mA Value],

[Flow Ref], [Pulse Value],[Pressure Ref], [Operating Pressure], [Operating Temperature], [Temp Ref], [Insertion Line Size], [Insertion Line Size 2] ,

[Remarks],[Order Detail Notes] ,[Tag Number],[Maximum Air Flow] ,[20 mA Air Flow] ,[Order Entered By],[Extrapolation] ,[Installation Factor],[Default Value Used] ,

[Mixed Gas Name] ,[Normal Density] ,[Standard Density] ,[Production Notes] ,[Flowcell Status],[Head/Ins Status] ,[Reschedule Date],[+31b],[+Deg] ,[+Stock],[+PresAssem] ,

[+Cal],[Cal Date],[UK Customer ?],[Revised Delivery],[On Hold],[Hold Comments]

from inserted

delete from prodschedulequery where [order id] = @orderid

insert into [ProdScheduleQuery] values (@OrderID, @ProdWeekend,@Day,@ProdWeek,@ProdDay,@Prog,@ProgDay,@CalWeekend,@CalDay,@Cal,@PresAssemWeekend,@AssemDay,@PresAssem,

@StockWeekend,@StockDay,@Stock)

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 3

It looks to me like you just need to get rid of the variables and put the calculations directly behind the last insert. It looks like it'll probably be a large statement (given some of your variables are derived from other variables so your calculations may have to be included more than once).

Along the similar lines of your first insert statement.

HTH.

Robert Craig Old Hand Points: 325 More actions · Answer 4

why would the insert run for each row updated but the calculations not?

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 5

Because the straight insert from the inserted table would be a set operation (i.e. would run once with multiple rows in one operation).

When you set the variables, they just store the value from the first row retrieved. That is, when you do your first select:

select @OrderID = [Order ID],

@DueDate = [Due Date],

@plus31b = [+31b],

@plusDeg = [+Deg],

@RescheduleDate = [Reschedule Date],

@plusCal = [+Cal],

@plusPresAssem = [+PresAssem],

@plusStock = [+Stock]

from inserted

It will populate each of those variables with the value returned from the first row of inserted. Your subsequent operations then set variables up with single values on based on those you just populated.

You then do an INSERT ... VALUES statement - which only ever inserts one row with the values you list.

So the second insert statement will only insert one row, with the values held in each of the variables.

Hope that makes sense.

Robert Craig Old Hand Points: 325 More actions · Answer 6

I feared that was the case when i first started writing the query hence tried to use a trigger instead. I have 12 calculations derived one after the other so my code is going to be huge. Is there no way around this?

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 7

Sorry... nothing that I can see to avoid the complication, with the calculations being derived (you can't refer back to other calculations in a SELECT statement).

The only solution I can think of off the top of my head would be to create a temp table with all the columns from [ProdScheduleQuery] in, insert the non-derived ones (allowing NULLs to fill the other columns), then perform an update statement to set the remaining columns to values derived from the first.

However, this is a bit messy and probably less efficient. You'll probably be better off writing the large insert statement straight off (with help from copy/paste), as you'll then not have to do it again, and if you present it well enough then it should be easy to edit too.

noeld SSC Guru Points: 96590 More actions · Answer 8

Robert,

1. Triggers should ALWAYS be coded to handle Multiple row insertion/delete/update

2. I see a pattern in your code so I believe you can probably arrive to formulas directly from the "base" columns

3. It is not that difficult to handle multiple nesting of subqueries. You just have to work a bit harder. I would still go for the FORMULAS that are independent from previous calculation but I don't know anything about your business rules

4. Anyways here is an example without variables:

CREATE TRIGGER trigOrderDetail ON [dbo].[Order Detail]

instead of insert,update

AS

insert into [order detail] ( [SAP ORDER NUMBER] , [Flowconditioner ID], [Item Number], [SAP Date], [Order Receipt Date], [Amendment Receipt Date],

[Due Date], [Amendment Number], [Amendment Notes], [Meter Part Number], [AMT Part Number], [AMT Serial Number],

[Flowcon Part Number],[AZT570 Part Number],[Mounting Boss Part Number] ,[Prefix To Serial No] ,[Gas Ref] , [Sensor Range] ,[20mA Value],

[Flow Ref], [Pulse Value], [Pressure Ref], [Operating Pressure], [Operating Temperature], [Temp Ref], [Insertion Line Size], [Insertion Line Size 2] ,