Sql server trigger if updating
Using the clause WHERE Order Status='Approved' by itself to limit the rows updated will actually result in all rows with an Order Status value of Approved being updated at the same time.To test the trigger, we will execute a T-SQL UPDATE statement to set the Order Status value to "Approved" for the first row in the table (pk ID = 1).First of all, let’s setup a test schema consisting of a Products table and a Product Price Log table to record every change to the price of every product. This use of the UPDATE function for selective logging can be very useful when used on tables with columns such as: Last Order Date, Last Login Date, etc as these columns are often updated but those changes are probably not required to be logged.CREATE TABLE Products ( Product ID INT IDENTITY(1,1), Name NVARCHAR(100), Price DECIMAL(19, 4) ); GO CREATE TABLE Product Price Log ( Product ID INT, Change Date Time DATETIME2, Old Price DECIMAL(19, 4), New Price DECIMAL(19, 4) ); GOCREATE TRIGGER trg_Log Product Price Change ON Products AFTER INSERT, UPDATE AS BEGIN IF(UPDATE(Price)) BEGIN BEGIN TRY INSERT Product Price Log SELECT i. One interesting point is that even if our trigger was configured to fire on DELETEs, the UPDATE function would not return true and therefore the change would not be logged.
However, we need to run a second test to ensure that the correct number of rows have been updated.
We populate the table with three test records with the Order Approval Date Time set to NULL and the Order Status set to "Pending".
In this example, we want our trigger to populate the Order Approval Date Time with the current date provided by the getdate() function after the Order Status is updated to "Approved".
We will create a trigger which will incorporate all three of the methods.
Customers and we want to check for updates on its first column, Last Name.