Increment in a Trigger

LinksUp · #1 (**permalink**) 02-23-13, 16:49

Not that this is recommended, but more for learning.

I have the following table:

Code:

CREATE TABLE [dbo].[apcName](
	[SysID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[RIN] [int] NOT NULL,
	[Name] [varchar](80) NOT NULL,
	[DimTab] [tinyint] NOT NULL,
	[MDateTime] [datetime] NOT NULL,
	[MUser] [varchar](128) NOT NULL,
 CONSTRAINT [APCN_SysIDX] PRIMARY KEY CLUSTERED 
(
	[SysID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
         ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [APCN_RINX] ON [dbo].[apcName] 
(
	[RIN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
        SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
        DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
        ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

ALTER TABLE [dbo].[apcName] ADD CONSTRAINT
 [DF_apcName_RIN]  DEFAULT ((0)) FOR [RIN]

If I have an Insert trigger to set the RIN field to the next available number in the sequence of the RIN column, why does the following work for inserting 1 and only 1 record at a time but fails if doing multiple rows with a single insert?

Code:

   -- Insert Trigger
   UPDATE xRec 
	      SET RIN = (Select MAX(RIN) + 1 from apcName)
             FROM apcName xRec
	     JOIN inserted ins
	       ON ins.sysID = xRec.sysID

-- Works, RIN is populated correctly

Code:

  Insert apcName (Name, DimTab) VALUES ('Test Name', 1)

Does Not Work. Returns 1 for all RIN's

Code:

  Insert apcName (Name, DimTab) VALUES 
      ('Test Name1', 1),
      ('Test Name2', 2)

blindman · #2 (**permalink**) 02-23-13, 22:57

Because max(RIN) in the trigger is run only once, not once for each row updated. That same value it then assigned to all the records inserted, resulting in failure.
I hope you aren't seriously implementing this in a production system?

LinksUp · #3 (**permalink**) 02-24-13, 00:00

Quote:

Originally Posted by blindman

.
I hope you aren't seriously implementing this in a production system?

No, I am not. Like I said at the top of the OP, this was more of an exercise to see what works and what does not and the why's behind it.

What would you suggest?

weejas · #4 (**permalink**) 02-25-13, 06:38

Coming at this from a VBA background, I would suggest that you need to loop through the contents of Inserted, and increment RIN each loop.
There's probably a better way, though.

Pat Phelan · #5 (**permalink**) 02-25-13, 09:26

Ok, I'll grant you that this degenerates into a trivial case but could you use:

Code:

-- Insert Trigger
   UPDATE xRec 
      SET RIN = xRec.sysID
      FROM apcName xRec
      JOIN inserted ins
         ON ins.sysID = xRec.sysID

-PatP

LinksUp · #6 (**permalink**) 02-25-13, 10:02

Quote:

Originally Posted by Pat Phelan

. . . but could you use:

Code:

-- Insert Trigger
   UPDATE xRec 
      SET RIN = xRec.sysID
 . . .

This fails. Probably for the same reason stated by Blindman.

Pat Phelan · #7 (**permalink**) 02-25-13, 11:33

I'm 99.5% sure that your problem occurs long before the trigger fires. Try this piece of code, then read on once you've checked its output to be sure that you agree that it works.

Code:

DROP TABLE apcName
GO
CREATE TABLE [dbo].[apcName](
   [SysID]       [int]          NOT NULL
      IDENTITY(1, 1) NOT FOR REPLICATION 
,  [RIN]         [int]          NOT NULL
      CONSTRAINT [DF_apcName_RIN]
         DEFAULT (0)
,  [Name]        [varchar](80)  NOT NULL
,  [DimTab]      [tinyint]      NOT NULL
,  [MDateTime]   [datetime]     NOT NULL
      CONSTRAINT [DF_apcName_MDateTime]
         DEFAULT (GetUTCDate())
,  [MUser]       [varchar](128) NOT NULL
      CONSTRAINT [DF_apcName_MUser]
         DEFAULT ('default'),

   CONSTRAINT [APCN_SysIDX] PRIMARY KEY CLUSTERED 
(
	[SysID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
         ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [APCN_RINX] ON [dbo].[apcName] 
(
	[RIN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
        SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
        DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
        ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
--  ptp  20130225  Demo INSERT trigger

CREATE TRIGGER tiapcName
   ON apcName
   FOR INSERT
AS
BEGIN

-- Insert Trigger
   UPDATE xRec 
      SET
         RIN = xRec.sysID
,        MDateTime = GetUTCDate()
,        MUser = user_name()
      FROM apcName xRec
      JOIN inserted ins
         ON ins.sysID = xRec.sysID

END
GO

--  The original fails

Insert apcName 
   (Name, DimTab)
   VALUES 
      ('Test Name1', 1)
,     ('Test Name2', 2)
GO

--  This one works

Insert apcName 
   (Name, DimTab, RIN)
   VALUES 
      ('Test Name3', 3, -33)
,     ('Test Name4', 4, -44)
GO

SELECT *
   FROM apcName

The unique constraint is the Achilles heal, stopping the insert before the trigger even gets a chance to start. The trigger isn't part of the problem as far as I can tell.

-PatP

LinksUp · #8 (**permalink**) 02-25-13, 22:16

Quote:

The unique constraint is the Achilles heal, stopping the insert before the trigger even gets a chance to start. The trigger isn't part of the problem as far as I can tell.

You are absolutely correct about the UNIQUE constraint being the problem. Removing UNIQUE allowed the trigger code to execute with the expected results.

The Insert has obviously happened and the data validated against its constraints before the trigger gets it. So what is needed is a BEFORE INSERT that happens after the identity increment, but before constraint checking. (I don't understand the INSTEAD clause yet to know if this is what I would want.)

On a side note. With UNIQUE turned on, the first insert of 2 records fails as expected. The second insert of 2 records succeeds but the identity column is set to 3 & 4! So I tried surrounding the first insert with Begin Transaction & Rollback Transaction and got the same results. The identity column is independent of the transaction.

Interesting stuff.

Thank you.

Pat Phelan · #9 (**permalink**) 02-25-13, 23:34

You can't do an INSTEAD OF trigger on the table itself that will accomplish what you want, but you can dance neatly around it by:

Renaming the table to XapcName
Creating a view named apcName on XapcName.
Creating an INSTEAD OF trigger on the apcName view that does an INSERT XapcName

This ought to be enough to make you dizzy, but it also ought to work! Once you get to that point, you can wrestle with how in tarnation you want to handle the IDENTITY problem!

-PatP

LinksUp · #10 (**permalink**) 02-26-13, 13:13

Quote:

Originally Posted by Pat Phelan

This ought to be enough to make you dizzy

That it does!

LinksUp · #11 (**permalink**) 02-26-13, 13:38

Taking what I have learned from this thread and applying it to another similar problem is not working as I had hoped. The business requirement is that the names associated for any 1 account (EntryNumber) must remain in the order they were entered. How do I get the next available sequence number for a sub set of the rows from within an Insert trigger?

Code:

DROP TABLE [dbo].[Grantees]
CREATE TABLE [dbo].[Grantees]
(
	[RIN] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[EntryNumber] [int] NOT NULL,
	[NameRIN] [int] NOT NULL,
	[Sequence] [int] NOT NULL
      CONSTRAINT [DF_Grantees_Sequence]
         DEFAULT (0),
 CONSTRAINT [TEE_RINX] PRIMARY KEY CLUSTERED 
(
	[RIN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [TEE_EntryNdx] ON [dbo].[Grantees] 
(
	[EntryNumber] ASC,
	[Sequence] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE TRIGGER [dbo].[GranteesTrigger]
   ON [dbo].[Grantees]
   AFTER INSERT
AS
BEGIN
        UPDATE G
           SET Sequence = 
           
           /*(select count(*) from Grantees T3, inserted t2 where T2.EntryNumber=T3.EntryNumber and T2.Sequence >= T3.Sequence),*/
           /*
			  (SELECT count(*) FROM Grantees G1, inserted ins
				WHERE ins.EntryNumber = G1.EntryNumber AND ins.sequence <= G1.sequence
			   ),
           */
              (
               Select ISNULL(MAX(G1.Sequence), 0) + 1  
                 From Grantees G1, inserted i
                Where G1.EntryNumber = i.EntryNumber
              )
          FROM Grantees G
          JOIN inserted i ON i.RIN = G.RIN
END
GO

Test Data:

Code:

Insert Grantees (EntryNumber, NameRIN)
VALUES
	(1, 1)
,	(1, 6)
,	(1, 4)
,	(2, 5)
,	(3, 8)
,	(3, 4)

Desired Output:

Code:

Entry Number              NameRIN           Sequence
1                               1                     1
1                               6                     2
1                               4                     3
2                               5                     1
3                               8                     1
3                               4                     2

Any suggestions are appreciated.

Thanks.

Pat Phelan · #12 (**permalink**) 02-26-13, 15:08

Danger Will Robinson! Madness lurks past this point!!!

Danger, danger, danger!!!

Code:

DROP TABLE [dbo].[Grantees]
GO
CREATE TABLE [dbo].[Grantees]
   (
   [RIN]            [int] NOT NULL IDENTITY(1, 1)
      NOT FOR REPLICATION
,  [EntryNumber]    [int] NOT NULL
,  [NameRIN]        [int] NOT NULL
,  [Sequence]       [int] NOT NULL
      CONSTRAINT [DF_Grantees_Sequence]
         DEFAULT (0),

   CONSTRAINT [TEE_RINX]
      PRIMARY KEY CLUSTERED 
         (
         [RIN] ASC
         )
         WITH 
		 (  PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
,           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON
,           ALLOW_PAGE_LOCKS  = ON
         ) ON [PRIMARY]
   ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [TEE_EntryNdx]
   ON [dbo].[Grantees] 
   (
   [EntryNumber] ASC
,  [Sequence] ASC
   )
   WITH 
   (  PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
,     SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
,     DROP_EXISTING = OFF, ONLINE = OFF
,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
   ) ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[GranteesTrigger]
   ON [dbo].[Grantees]
   AFTER INSERT
AS
BEGIN
   UPDATE g
      SET Sequence = z.s
      FROM Grantees AS g
      JOIN inserted AS i
         ON (i.RIN = g.RIN)
      JOIN (SELECT z1.RIN
,        Row_Number() OVER (PARTITION BY z1.EntryNumber ORDER BY z1.RIN) AS s
         FROM Grantees AS z1) AS z
            ON (z.RIN = i.RIN)
END
GO
Insert Grantees (EntryNumber, NameRIN, Sequence)
VALUES
	(1, 1, -1)
,	(1, 6, -2)
,	(1, 4, -3)
,	(2, 5, -4)
,	(3, 8, -5)
,	(3, 4, -6)
GO
SELECT *
   FROM Grantees

...but it works!

-PatP

MCrowley · #13 (**permalink**) 02-26-13, 15:33

If the requirement is to keep the contacts in chronological order (order of entry), then why not have the report or whatever select query order by the entry or create date on the table?

LinksUp · #14 (**permalink**) 02-26-13, 15:48

Quote:

Originally Posted by MCrowley

then why not have the report or whatever select query order by the entry or create date on the table?

I was trying to reduce the problem to its minimum. The user has the option of adding more rows and then rearranging them to suit their particular needs. At a later date.
Renumbering an existing group of records is not the difficult part. The hard part was getting a correct sequence number during the Insert process in the trigger. (The initial entry of the rows will probably remain stable for 95% of the entries. It is the other 5% that get rearranged and added to that I have to worry about!!)

Pat Phelan · #15 (**permalink**) 02-26-13, 15:52

Quote:

Originally Posted by MCrowley

If the requirement is to keep the contacts in chronological order (order of entry), then why not have the report or whatever select query order by the entry or create date on the table?

The ties that bind... The process up.

When umpty bazillion rows get inserted in one swell foop, they all will have the same date (down to the millisecond). The business requirement is that every row within a group have a sequential "serial number" within that group.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech