Update Trigger

Update Trigger for update the specific column

Create Update Trigger to update a one particular column in the table while updating the record.

According to the specific value of another column we are updating the other column.

Create new table

CREATE TABLE [dbo].[CustomerInvoice](

      [InvoiceID] [int] IDENTITY(1,1) NOT NULL,
      [InvoiceDate] [date] NULL,
      [InvoiceAmount] [numeric](10, 2) NULL,
      [PaidAmount] [numeric](10, 2) NULL,
      [BalanceAmount] [numeric](10, 0) NULL,
      [InvoiceSetteled] [bit] NULL,
 CONSTRAINT [PK_InvoiceMaster] PRIMARY KEY CLUSTERED
(
      [InvoiceID] ASC
))

GO

Some records for Invoice data before update

Create Trigger

CREATE TRIGGER UpdateSetteledStatus ON dbo.CustomerInvoice

AFTER UPDATE

AS

BEGIN

  SET NOCOUNT ON;

  DECLARE @InvID int

  IF (UPDATE(BalanceAmount))

    BEGIN

      SELECT @InvID = InvoiceID FROM INSERTED

      IF(SELECT BalanceAmount FROM dbo.CustomerInvoice WHERE InvoiceID = @InvID)

         = 0

            UPDATE dbo.CustomerInvoice SET InvoiceSetteled = 1 WHERE InvoiceID =

            @InvID

    END

END

GO

Update one record in the table

UPDATE dbo.CustomerInvoice SET PaidAmount = 2800,BalanceAmount = 0 where InvoiceID = 3

Updating the record after settled the Invoice, as PaidAmount = InvoiceAmount and BlanceAmount = 0.  

After update check the below records.

 

In this case after update this perticular record with Balance Amount as 0 and UPDATE TRIGGER has fired and update the InvoiceSettled column as 1.