After insert trigger to change inserted record values

After insert trigger to change inserted record values

Post by Larry Kill » Tue, 29 Jan 2008 03:18:41


The trigger below won't work but is included to show what I want to do.
Whenever a record is added to my Infeed table, I want to run Stored
Procedure spGetShiftProdDate and insert the values for the shift and product
date. It woudl seem that I would want to change the value of ProdWorkDate
and Shift for the newly inserted record BEFORE the record was written to the
database but I cannot figure out how to do this. I left my WROX at the
office and BOL is not telling me what I need to know. Any help will be
appreciated.



Thanks



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

go

-- =============================================
-- Author: Larry Killen
-- Create date: 1/27/2008
-- Description: Inserts ProdWorkDate and shift into record
-- =============================================

ALTER TRIGGER [trgSetProdWorkDateShift]
ON [dbo].[Infeed]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RC int
DECLARE @currentdatetime datetime
DECLARE @shift tinyint
DECLARE @startshiftadjdt datetime
DECLARE @stopshiftadjdt datetime
DECLARE @prodworkdate datetime

Set @currentdatetime = (select WeighTime from inserted)

EXECUTE @RC = [YieldPlusNG].[dbo].[spGetShiftProdDate]

@currentdatetime
,@shift OUTPUT
,@startshiftadjdt OUTPUT
,@stopshiftadjdt OUTPUT
,@prodworkdate OUTPUT

If @RC = 0
Begin
Update inserted
Set Shift = @shift, ProdWorkDate = @prodworkdate <<= how to accomplish
this??
End

END

USE [YieldPlusNG]
GO
/****** Object: Table [dbo].[Infeed] Script Date: 01/27/2008 13:11:52
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Infeed](
[Shift] [tinyint] NULL,
[Station] [smallint] NOT NULL,
[IntEmpID] [int] NOT NULL,
[Weight] [decimal](10, 7) NOT NULL,
[WeighTime] [datetime] NOT NULL,
[ProdWorkDate] [datetime] NULL,
[ProdID] [tinyint] NOT NULL CONSTRAINT [DF_Infeed_ProdID] DEFAULT ((0))
) ON [PRIMARY]
 
 
 

After insert trigger to change inserted record values

Post by David Port » Tue, 29 Jan 2008 03:46:20


A trigger fires once per STATEMENT not once per row. Your trigger will fail
with an error if more than one row is inserted. Don't ever write triggers
like that.

What is/are the key(s) of this table? According to your DDL it doesn't have
one but assuming that's an oversight you can update the table like this:

UPDATE Infeed
SET ...
WHERE EXISTS
(SELECT 1
FROM inserted
WHERE key_col = Infeed.key_col);

I suggest you consider replicating whatever code is spGetShiftProdDate
within the trigger because I guess you will want to return different output
values for each affected row.

Alternatively, you could use procs instead of triggers to do the whole
thing. I find it good practice to avoid performing updates in triggers if at
all possible.

--
David Portas

 
 
 

After insert trigger to change inserted record values

Post by Alex Kuzne » Tue, 29 Jan 2008 05:18:04


Can you store start/end of shifts in a table and simply select from it
joined with your inserted virtual table?
If you can, that would significantly simplify your problem, and your
trigger will handle multi-row inserts just as well as single row ones.
 
 
 

After insert trigger to change inserted record values

Post by Larry Kill » Tue, 29 Jan 2008 12:35:03

I have it working. I could not do it your way since the ProdWorkDate and
Shift are resolved with a fairly complicated Stored Proc.

Thanks
Larry
 
 
 

After insert trigger to change inserted record values

Post by --CELKO- » Tue, 29 Jan 2008 12:43:37

>> could not do it your way since the ProdWorkDate and Shift are resolved with a fairly complicated Stored Proc. <<

Just based on a few decades of writing SQL, unless this stored
procedure is NP-complete and/or non-deterministic , I would bet that
you can do a look-up table for it. Then the worst case would a UDF
call in the VALUES() clause of a simple INSERT INTO statement without
using a cursor.