Prev: Sum and aggregations
Next: Need advice with ISNULL
From: Usman on 21 Apr 2010 08:40
Hi Guy,

Trigger below is not updating ODS_INST_ID column as it should when new
row is inserted. This behavior happens when new row is a copy of
existing row i.e. source row already has data in ODS_INST_ID. So for
example if source row has ODS_INST_ID = 1, copied row should have
ODS_INST_ID = 2 but I see ODS_INST_ID = 1.

Data is getting copied in a table by an application. Strangely, if 4
rows are to be copied, trigger works only for 1 row.

Any idea what may wrong with the trigger below?

Thanks,
Usman

USE [UK8]
GO
/****** Object: Trigger [dbo].[EQUIP_ods_inst_id_trig] Script
Date: 04/21/2010 16:51:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[EQUIP_ods_inst_id_trig] ON [dbo].[EQUIP]
FOR INSERT AS
begin
SET NOCOUNT ON
DECLARE @PROC_KEYTAG VARCHAR (10)
DECLARE @PROC_PROJID VARCHAR (100)
DECLARE @INSTID numeric(20)
SELECT @PROC_KEYTAG = KEYTAG FROM INSERTED
SELECT @PROC_PROJID = PROJ_ID FROM INSERTED

select @INSTID = MAX(ODS_INST_ID) from EQUIP
if @INSTID is null
Begin
set @instid = 0
end
set @INSTID = @INSTID + 1
UPDATE EQUIP SET ODS_INST_ID = @INSTID WHERE KEYTAG = @PROC_KEYTAG and
PROJ_ID = @PROC_PROJID
SET NOCOUNT OFF
End
GO
From: Uri Dimant on 21 Apr 2010 09:29
Usman
What if virtual inserted table will contain more than one row, you are about
to get wrong data
Why not join EQUIP and inserted table on key and update the column

Also , there is no need to wrtite two SELECT <> FROM insterted table as
you can

SELECT @PROC_KEYTAG = KEYTAG,
@PROC_PROJID = PROJ_ID
FROM INSERTED

But again the above is could be wron as the table might contain more than
one value



"Usman" <khanusman(a)gmail.com> wrote in message
news:61165a80-d903-4cc2-8b54-fa84c1cb0020(a)v20g2000yqv.googlegroups.com...
> Hi Guy,
>
> Trigger below is not updating ODS_INST_ID column as it should when new
> row is inserted. This behavior happens when new row is a copy of
> existing row i.e. source row already has data in ODS_INST_ID. So for
> example if source row has ODS_INST_ID = 1, copied row should have
> ODS_INST_ID = 2 but I see ODS_INST_ID = 1.
>
> Data is getting copied in a table by an application. Strangely, if 4
> rows are to be copied, trigger works only for 1 row.
>
> Any idea what may wrong with the trigger below?
>
> Thanks,
> Usman
>
> USE [UK8]
> GO
> /****** Object: Trigger [dbo].[EQUIP_ods_inst_id_trig] Script
> Date: 04/21/2010 16:51:00 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TRIGGER [dbo].[EQUIP_ods_inst_id_trig] ON [dbo].[EQUIP]
> FOR INSERT AS
> begin
> SET NOCOUNT ON
> DECLARE @PROC_KEYTAG VARCHAR (10)
> DECLARE @PROC_PROJID VARCHAR (100)
> DECLARE @INSTID numeric(20)
> SELECT @PROC_KEYTAG = KEYTAG FROM INSERTED
> SELECT @PROC_PROJID = PROJ_ID FROM INSERTED
>
> select @INSTID = MAX(ODS_INST_ID) from EQUIP
> if @INSTID is null
> Begin
> set @instid = 0
> end
> set @INSTID = @INSTID + 1
> UPDATE EQUIP SET ODS_INST_ID = @INSTID WHERE KEYTAG = @PROC_KEYTAG and
> PROJ_ID = @PROC_PROJID
> SET NOCOUNT OFF
> End
> GO


From: Usman on 21 Apr 2010 10:06
Hi Uri,

Thanks for the reply.

Problem is that I am getting duplicate ODS_INST_ID. I wont have a
problem if ODS_INST_ID gets incremented by more then 1.

Any reason you can think of ODS_INST_ID getting duplicate values?

Thanks,
Usman

On Apr 21, 6:29 pm, "Uri Dimant" <u...(a)iscar.co.il> wrote:
> Usman
> What if virtual inserted table will contain more than one row, you are about
> to get wrong data
> Why not join EQUIP  and inserted table on key and  update the column
>
> Also , there is no need  to wrtite two SELECT <> FROM insterted table as
> you can
>
>  SELECT @PROC_KEYTAG = KEYTAG,
>                 @PROC_PROJID = PROJ_ID
>  FROM INSERTED
>
> But again the above is could be wron as the table might contain more than
> one value
>
> "Usman" <khanus...(a)gmail.com> wrote in message
>
> news:61165a80-d903-4cc2-8b54-fa84c1cb0020(a)v20g2000yqv.googlegroups.com...
>
> > Hi Guy,
>
> > Trigger below is not updating ODS_INST_ID column as it should when new
> > row is inserted. This behavior happens when new row is a copy of
> > existing row i.e. source row already has data in ODS_INST_ID. So for
> > example if source row has ODS_INST_ID = 1, copied row should have
> > ODS_INST_ID = 2 but I see ODS_INST_ID = 1.
>
> > Data is getting copied in a table by an application. Strangely, if 4
> > rows are to be copied, trigger works only for 1 row.
>
> > Any idea what may wrong with the trigger below?
>
> > Thanks,
> > Usman
>
> > USE [UK8]
> > GO
> > /****** Object:  Trigger [dbo].[EQUIP_ods_inst_id_trig]    Script
> > Date: 04/21/2010 16:51:00 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > CREATE TRIGGER [dbo].[EQUIP_ods_inst_id_trig] ON [dbo].[EQUIP]
> > FOR INSERT AS
> > begin
> > SET NOCOUNT ON
> > DECLARE @PROC_KEYTAG VARCHAR (10)
> > DECLARE @PROC_PROJID VARCHAR (100)
> > DECLARE @INSTID numeric(20)
> > SELECT @PROC_KEYTAG = KEYTAG FROM INSERTED
> > SELECT @PROC_PROJID = PROJ_ID FROM INSERTED
>
> > select @INSTID = MAX(ODS_INST_ID) from EQUIP
> > if @INSTID is null
> > Begin
> > set  @instid  = 0
> > end
> > set @INSTID = @INSTID + 1
> > UPDATE EQUIP SET ODS_INST_ID = @INSTID WHERE KEYTAG = @PROC_KEYTAG and
> > PROJ_ID = @PROC_PROJID
> > SET NOCOUNT OFF
> > End
> > GO

From: Plamen Ratchev on 21 Apr 2010 10:44
Try this:

WITH UpdateCTE AS (
SELECT E.ODS_INST_ID,
COALESCE((SELECT MAX(ODS_INST_ID) FROM EQUIP), 0) +
ROW_NUMBER() OVER(ORDER BY E.KEYTAG, E.PROJ_ID) AS new_ods_inst_id
FROM EQUIP AS E
JOIN Inserted AS I
ON E.KEYTAG = I.KEYTAG
AND E.PROJ_ID = I.PROJ_ID)
UPDATE UpdateCTE
SET ODS_INST_ID = new_ods_inst_id;

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 21 Apr 2010 11:56
This is very confusing. Why do you have a trigger for this at all?
SQL is a declarative language and using procedural code to correct an
error after the fact is bad programming; we don't deliberately put in
bad data at the start.

1) If you have commodity data, then a column with the count of
occurrences should be incremented. You do not store redundant data in
a properly designed database. The skeleton is:

UPDATE Foobar
SET foo_cnt = foo_cnt +1
WHERE <duplicate data test>;

2) If you want to increment an identifying attribute column in an
inserted row, you do it in the INSERT INTO statement, instead of
patching it with (ugh!) procedural code after the fact. The skeleton
is:

INSERT INTO Foobar (foo_key, ..)
VALUES ( MAX(foo_key) OVER() +1, ..);

3) If you can have both situations, then use a MERGE statement.
 | 
Pages: 1
Prev: Sum and aggregations
Next: Need advice with ISNULL