SQL Server 2012 Trigger – How to Update Another Table When Inserting and Deleting

sql-server-2012

I need help with modifiying this trigger. I'm new to triggers. When it's executed it is supposed either to delete 1 from nrOfCopies or add 1 to nrOfCopies on a based on the specific isbn(a char(20)), but this does it on all the rows. Becuase its a trigger i cannot use a parameter.

ALTER TRIGGER trgUpdatesNrOfCopies
ON bookcopy
AFTER INSERT, DELETE
AS
BEGIN
   SET NOCOUNT ON

   IF NOT EXISTS(SELECT*FROM INSERTED)
   BEGIN    
       PRINT 'DELETE'
       UPDATE book
       SET nrOfCopies=(SELECT nrOfCopies FROM bookcopy WHERE isbn )
   END
   ELSE IF NOT EXISTS(SELECT * FROM DELETED)
   BEGIN
       PRINT 'INSERT'
       UPDATE book
       SET nrOfCopies=nrOfCopies+1
   END
END

The column I need to specify is called isbn. How can I specify it to only make the changes on one specific isbn column?

When this trigger is being fired i get 'Primary key violation' error message. I dont know how to fix this!

Best Answer

Something along the lines of the below... just join to the inserted/deleted tables. Please do test this though with real life scenarios:

ALTER TRIGGER trgUpdatesNrOfCopies
ON bookcopy
AFTER INSERT, DELETE
AS
BEGIN
   SET NOCOUNT ON

   IF NOT EXISTS(SELECT*FROM INSERTED)
   BEGIN    
       PRINT 'DELETE'
       UPDATE b
       SET nrOfCopies= nrOfCopies-1
       from book b
       inner join DELETED d
         on d.isbn = b.isbn
   END
   ELSE IF NOT EXISTS(SELECT * FROM DELETED)
   BEGIN
       PRINT 'INSERT'
       UPDATE b
       SET nrOfCopies=nrOfCopies+1
       from book b
       inner join inserted i
         on i.isbn = b.isbn
   END
END

Related Solutions

Sql-server – Page Split Timing

The UPDATE would happen after the split because from a data state perspective, SQL Server will never overwrite another currently-allocated row in the process.

Moreover, if SQL Server did overwrite a portion of another row, and that row had to be moved, it wouldn't know what data to copy to the new page. A copy of the row could be kept in a temporary buffer in memory... which... is the very definition of a data page.

And so the splitting process goes as follows:

Allocate a new page
Copy the split rows to the new page
Deallocate the split rows from the original page
Did we reach at least the target amount of free space? If yes, we're done; if no, split again.

Finally, the UPDATE occurs, which is always free to overwrite unallocated portions of the page.

Sql-server – Moving rows from one table to another

I would recommend TRY/CATCH syntax along with explicit transactions. My assumption for this solution is that the reason the for the insert failure is some sort of trappable SQL error (such as a key violation, datatype mismatch/conversion error, etc.). The structure would look like this:

BEGIN TRAN

BEGIN TRY
  INSERT INTO foo(col_a,col_b,col_c,recdate)
  SELECT col_a,col_b,col_c,recdate
  FROM bar
  WHERE recdate BETWEEN @startdate AND @enddate

  DELETE FROM bar
  WHERE recdate BETWEEN @startdate AND @enddate

  COMMIT TRAN
END TRY
BEGIN CATCH
  ROLLBACK TRAN
END CATCH

The way this structure works, if any error occurs in the INSERT or the DELETE, the entire action gets rolled back. This guarantees that the entire action must be successful to be completed. If you felt that it was necessary, you could combine it with THROW for 2012 or RAISERROR in 2008 and previous to add additional logic and force a rollback if that logic wasn't met.

Another option is to look at SET XACT_ABORT ON, though I feel that the TRY/CATCH syntax gives you more granularity.

Best Answer

Related Solutions

Sql-server – Page Split Timing

Sql-server – Moving rows from one table to another

Related Question