Write a trigger that fires anytime an insert or update occurs on the Invoice tab
ID: 3718466 • Letter: W
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
The IF - ELSE BLOCK is messed up a bit
I have rectified your code - you can check, also ensure to add BEGIN - END pair, wherever required, while grouping lines of code in IF, ELSE and loops, to avoid errors.
Syntax of your code is as follows -
IF condition
BEGIN
// code when condition is true
END
ELSE
BEGIN
// code when condition is false
END
\use above syntax and copy your code in places whereever is required.
Actual code is -
BEGIN TRAN;
INSERT into Vendors (VendorName, VendorCity, VendorState, VendorZipCode)
VALUES ('Hubert', 'Harrison', 'Ohio', '45030')
IF @@error <> 1
BEGIN
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 'ERROR'
RETURN ;
END
ELSE
BEGIN
COMMIT TRAN;
PRINT 'SUCCESSFUL - CATCH'
RETURN;
END
END
ELSE
BEGIN
COMMIT TRAN;
PRINT 'SUCCESSFUL'
RETURN;
END
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.