Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    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)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    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

  3. #3
    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?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,905
    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.

  6. #6
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,905
    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.

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    162
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,905
    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.

  10. #10
    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!

  11. #11
    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.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,905
    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.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,501
    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?

  14. #14
    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!!)

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,905
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •