Question
Basics for the assignment 0. Necessary Style* (please see below) 1. Create the table T1 owned by dbo per the specs for the T1 table
Basics for the assignment 0. Necessary Style* (please see below) 1. Create the table T1 owned by dbo per the specs for the T1 table in the Auditing Example in Chapter 4. Create a table owned by dbo named T1Audit by modifying the T1Audit table definition from the book in chapter 4 by adding a DATETIME column named ModificationTime. Make it the 2nd column right after the lsn column and make it so that it does not accept null values. 2. Create a new version of the authors FOR UPDATE trigger so that it also populates our new ModificationTime column in addition to the other columns from the authors example. The name of the trigger must be T1Update. 3. Create two additional audit triggers to complement the authors FOR UPDATE trigger on table T1. The triggers should be part of the dbo schema. a. Create a FOR DELETE trigger named T1Delete. b. Create a FOR INSERT trigger named T1Insert. The two new triggers should also populate T1Audit table including our additional column, ModificationTime. 4. Ensure that both T1 is empty and T1Audit is empty. You will be graded on the exact contents of both tables. 5. Now insert two rows into the T1 table with the following values keycol intcol varcharcol 1 1 DSU 2 2 Trojan 6. Execute an update statement against T1 setting the varcharcol value to Madison for the row with keycol 2. 7. Delete the row with keycol value 1. 8. End the script with the statement generating the correct results SELECT * FROM dbo.T1Audit ORDER BY ModificationTime; *Necessary style Every object that you create (table, trigger, etc.) must include a check for an existing object with the same name, and if the object exists then drop it. You will also need to include GO commands in the appropriate locations. A good test is to execute your script multiple times you should be able to do that without any errors and always producing the same expected results as shown in Figure 1 below.
keycol colname newval Isn Modification Time 1 2017-01-26 19:33:33.757 1 2 2017-01-26 19:33:33.757 1 3 2017-01-26 19:33:33.7602 4 2017-01-26 19:33:33.760 2 5 2017-01-26 19:33:33.770 2 6 2017-01-26 19:33:33.787 1 7 2017-01-26 19:33:33.787 1 NULL 1 intcol varcharcol NULL DSU intcol varcharcol NULL Trojan varcharcol Trojan Madison intcol varcharcol DSU NULL NULL 2 NULL Figure 1 - Expected Results (with different ModificationTime values)Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started