Trigger Exercise Write a trigger that fires anytime an insert or update occurs o

Question

Trigger Exercise

Write a trigger that fires anytime an insert or update occurs on the Invoice table. The trigger will need to write the following information to a ChangeLog table.

ChangeID int   identity(1,1) primary key
InvoiceID       int                             (will contain the ID of the invoice being changed)
ChangedBy          nvarchar(30)           (will contain the login of the user making the change)
DateChanged          smalldatetime        (will contain the date of the change)
OldInvoiceTotal    money                     (will contain the old value found in the deleted temp table)
NewInvoiceTotal money                     (will contain the new value found in the inserted temp table)

On an insert both the OldInvoiceTotal and the NewInvoiceTotal will be the same. Make sure you test your trigger for and update situation where the InvoiceTotal is changed. This trigger should fire off when you run your stored procedure from above. This table is very similar to the one you created for the Northwind trigger exercise.

Invoices AS
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,
Customers.Region, Customers.PostalCode, Customers.Country

Note: Name your trigger as Test02_tr.

Explanation / Answer

Triggers run as an integrated unit, hence you should have provided details for the Northwind trigger exercise. I have tried to create a trigger from the information you have provided, let's see it works well on your machine and also change {new_name} with the name of your table.

the code is as below-

CREATE TRIGGER Test02_tr AFTER INSERT ORDER 4 ON t0
REFERENCING NEW AS new_name
FOR EACH STATEMENT
BEGIN
DECLARE @ChangeID INTEGER as primary;
DECLARE @InvoiceID INTEGER;
DECLARE @ChangedBy NVARCHAR;
DECLARE @DateChanged TIMESTAMP;
DECLARE @OldInvoiceTotal MONEY;
DECLARE @NewInvoiceTotal MONEY;
DECLARE @err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
//declare a cursor for table new_name
DECLARE new1 CURSOR FOR
SELECT ChangeID, InvoiceID, ChangedBy,DateChanged,OldInvoiceTotal,NewInvoiceTotal FROM new_name;
OPEN new1;
//Open the cursor, and get the value
LoopGetRow:
LOOP
FETCH NEXT new1 INTO @ChangeID,@InvoiceID,@ChangedBy,@DateChanged,@OldInvoiceTotal,@NewInvoiceTotal
IF SQLSTATE = @err_notfound THEN
LEAVE LoopGetRow
END IF;
//print the value or for other use
PRINT (@remarks1);
END LOOP LoopGetRow;
CLOSE new1
END;

Hope this code will work fine else more information needed to create the trigger.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.