If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Increment in a Trigger

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
Increment in a Trigger

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)
Reply With Quote
  #2 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,523
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?
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
Quote:
Originally Posted by blindman View Post
.
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?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 959
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.
__________________
10% of magic is knowing something that no-one else does. The rest is misdirection.
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,445
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
Quote:
Originally Posted by Pat Phelan View Post
. . . but could you use:
Code:
-- Insert Trigger
   UPDATE xRec 
      SET RIN = xRec.sysID
 . . .
This fails. Probably for the same reason stated by Blindman.
Reply With Quote
  #7 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,445
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
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.
Reply With Quote
  #9 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,445
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:
  1. Renaming the table to XapcName
  2. Creating a view named apcName on XapcName.
  3. 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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
Quote:
Originally Posted by Pat Phelan View Post
This ought to be enough to make you dizzy
That it does!
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
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.
Reply With Quote
  #12 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,445
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #13 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,389
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?
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
Quote:
Originally Posted by MCrowley View Post
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!!)
Reply With Quote
  #15 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,445
Quote:
Originally Posted by MCrowley View Post
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
Reply

Tags
mssql, sql 2008

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On