Write a trigger that fires anytime an insert or update occurs on the Invoice tab

Question

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.

Note: Name your trigger as Test02_tr.

My answer is below and is not finished, thus requiring help:

BEGIN TRAN;
INSERT into Vendors (VendorName, VendorCity, VendorState, VendorZipCode)
VALUES ('Hubert', 'Harrison', 'Ohio', '45030')
IF @@error <> 1
BEGIN TRY
ROLLBACK TRAN
PRINT 'Failed Transaction.';
RETURN
END TRY
BEGIN CATCH
DECLARE @VendorID int;
SELECT @VendorID=@@identity
INSERT Invoices(InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, IncoiceDueDate, PaymentDate)
VALUES('115', '200', '123456', '2016-03-25 00:00:00', '2500.00', '1500.00', '0.00', '2', '2018-04-27 00:00:00', '2018-04-27 00:00:00');
INSERT InvoiceLineItems (InvoiceID, InvoiceSequence, AccountNo, InvoiceLineItemAmount, InvoiceLineItemDescription)
VALUES ('115', '2', '601', '1011.85', 'Freight');
IF @@error <> 1
BEGIN
ROLLBACK TRAN;
PRINT ''
RETURN
END;
ELSE
BEGIN
COMMIT TRAN
PRINT 'Failed Transaction.'
END

I am getting a syntax error when attempting to run this around the end statement.

Error reads:

Msg 102, Level 15, State 1, Line 27
Incorrect syntax near 'END'.

Explanation / Answer

You missed semicolon at after print statement...

if you have any doubts, please give me comment...

BEGIN TRAN;
INSERT into Vendors (VendorName, VendorCity, VendorState, VendorZipCode)
VALUES ('Hubert', 'Harrison', 'Ohio', '45030')
IF @@error <> 1
BEGIN TRY
ROLLBACK TRAN
PRINT 'Failed Transaction.';
RETURN
END TRY
BEGIN CATCH
DECLARE @VendorID int;
SELECT @VendorID=@@identity
INSERT Invoices(InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, IncoiceDueDate, PaymentDate)
VALUES('115', '200', '123456', '2016-03-25 00:00:00', '2500.00', '1500.00', '0.00', '2', '2018-04-27 00:00:00', '2018-04-27 00:00:00');
INSERT InvoiceLineItems (InvoiceID, InvoiceSequence, AccountNo, InvoiceLineItemAmount, InvoiceLineItemDescription)
VALUES ('115', '2', '601', '1011.85', 'Freight');
IF @@error <> 1
BEGIN
ROLLBACK TRAN;
PRINT ''
RETURN
END;
ELSE
BEGIN
COMMIT TRAN;
PRINT 'Failed Transaction.';
END

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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