CODEWITHSUNDEEP
sqlsql-serverdatabasetriggers
Thierry
Thierry

Reputation: 6458

SQL Server Trigger Update and Increment counter

I have two tables. One table has a single field in it which is used to simply hold a counter and the other one has numerous search fields and one of these fields must contain the counter value from the other table.

I'm trying to create a trigger that will

  1. increment the value by 1 in the first table when an update occurs in the second table
  2. the incremented value from the first table will be used to set a field in the second table
  3. 1 & 2 will only occur when an update occurs in the second table and a field i.e. Status is equal to a specific value.
  4. Handle 1,2 and 3 for multiple records.

1) I've managed to write this trigger but a) it seems terribly slow and b) I've found an article which explained it fairly well, but as the example showed, this is only for one record. It does provide a solution if multiple records are updated but my problem is how to I handle incrementing the value in the first table for each record and then update each individual record with a new value for each?

Any idea why it is slow (well, with sql server management console)?

The article I found can be found in http://benreichelt.net/blog/2005/12/13/making-a-trigger-fire-on-column-change/

The trigger I wrote based on this article is as follows:

CREATE TRIGGER [dbo].[trig_DataTable] ON [dbo].[DataTable]
FOR UPDATE
AS
IF UPDATE(Status)
BEGIN
SET NOCOUNT ON;

  DECLARE @newStatus NVARCHAR(50)
  DECLARE @oldStatus NVARCHAR(50)   
  DECLARE @maxRefNo INT

  DECLARE @id BIGINT

  SET @maxRefNo = (SELECT MAX(RefNo) FROM CounterTable) + 1
  UPDATE CounterTable SET RefNo = @maxRefNo

  SET @newStatus = (SELECT Status FROM Inserted)
  SET @oldStatus = (SELECT Status FROM Deleted)

  IF (@newStatus != @oldStatus) AND (@newStatus = 'Approved')
  BEGIN   
    SET @Id = (SELECT Id FROM Inserted)
    UPDATE DataTable
    SET UniqueRef = @maxRefNo
    WHERE Id = @Id
  END

END

One quick other question in regards to the above! Since the above trigger will only work for a single record, do I need to have the where part? It doesn't seems to make sense to need this.

Not very important as what I need is the same behaviour as above but it needs to handle it for multiple records being contained in the INSERTED/DELETED tables.

Could I write a function to get the next available number and update the CounterTable and then call this function from a similar SQL as explained in article

INSERT INTO PriceHistory(ItemId, OldPrice, NewPrice, UniqueRef)
 SELECT I.ItemId, D.Price, I.Price, dbo.GetNextRefNo()
 FROM INSERTED I INNER JOIN DELETED D ON I.ItemId = D.ItemId
 WHERE I.Price != D.Price

I'm not sure to be honest... I'll give that a shot in a second, but if any of you have an idea on how this can be achieved, please let me know... would be great! Thanks.

I hope the above is making sense.

Cheers.

T.

Upvotes: 1

Views: 9254

Answers (1)

mehdi lotfi
mehdi lotfi

Reputation: 11581

Use Below Query :

CREATE TRIGGER [dbo].[trig_DataTable] ON [dbo].[DataTable]
FOR UPDATE
AS BEGIN
    SET NOCOUNT ON;
    DECLARE @maxRefNo INT
    DECLARE @id BIGINT

    SET @maxRefNo = ISNULL((SELECT MAX(RefNo) FROM CounterTable),0)

    UPDATE CounterTable 
    SET RefNo = @maxRefNo + (SELECT COUNT(*) 
                            FROM INSERTED A 
                            INNER JOIN DELETED D ON D.id=A.id
                            WHERE A.STATE = 'Approved'
                              AND A.STATE <> D.State)

    UPDATE DataTable
    SET UniqueRef = @maxRefNo + ROW_NUMBER() OVER (ORDER BY A.id)
    FROM INSERTED A
    INNER JOIN DELETED D ON D.id = A.id
    WHERE DataTable.Id = A.Id
        AND A.STATE = 'Approved'
        AND A.STATE <> D.State
END

When you write a trigger use must coneive that trigger execute with multi record in inserted table. no one record.

Upvotes: 3

Related Questions