DRAG DROP
You are a SQL Server 2014 Developer. A database that you work on contains two tables
that are defined as follows:
Product is an important table that has sensitive audit requirements.
You need to create a trigger that supports the following requirements:
1. Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate
and LastUpdatedBy values in the Product table.
2. Any row that is updated or deleted must write a new record reflecting the OLD values into
the ProductAudit table.
3. Any error that occurs during the course of the trigger’s execution must prevent the
changes from happening.
Develop the solution by selecting and arranging the required code blocks in the correct
order.
You may not need all of the code blocks.
Answer: See the explanation
Explanation:
Box 1:Box 2:
Box 3:
Box 4:
Box 5:
Note:
* Executing a ROLLBACK TRANSACTION or COMMIT TRANSACTION Transact-SQL
statement inside a stored procedure or trigger is possible, but doing so may cause errors.
Create Trigger….
Update Product….Insert ProductAudit…
END
If you declare @old variables you are only dealing with one row and there may be multiple that the trigger handles.
The trigger is always part of the transaction for the action that fires the trigger. If an error occurs in the trigger that causes transaction rollback then the firing action will be rolled back too. There is no need for transactions. Triggers implicitly have XACT_ABORT on. An error with this setting on will automatically lead to transaction rollback. Plus, there is no BEGIN TRANSACTION statement to start with…
It’s right!
I copy Panos’, too.
There is no need for Variables or Transaction handling. They even cause errors.
Additional, in both select-statements of the insertion parts, the ‘sUser_Name()’ and the ‘getDate()’ are in the wrong order.
deleted is a table. can not assign a column to scalar variable.
box 2 need changed
also rollback should be before commit