Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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.

image text in transcribed

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Practical Issues In Database Management A Refernce For The Thinking Practitioner

Authors: Fabian Pascal

1st Edition

0201485559, 978-0201485554

More Books

Students also viewed these Databases questions

Question

Are my points each supported by at least two subpoints?

Answered: 1 week ago