IT-112 Audit Tables and Triggers

There are three(3) steps to making and using Audit Tables and Triggers:

1)Add strModified_Reason column to each table.
2)Add Z_< table> for each table.
3)Create Trigger for each table.

Create a single SQL script that will do the follow. Comment each step.

Problem 1:

Using the Script for provided with this assignment do the following.

Create Audit tables and Triggers for the 3 tables in the script.

1.Create a test for DELECT and UPDATE in each table in the following format

• DELETE FROM TTeams
• WHERE < some condition>
• SELECT * FROM TTeams
• SELECT * FROM Z_TTeams

2.After you test your calls and confirm they are working comment all test call lines out so just the script runs without the calls to DELETE and UPDATE (from step #1)

Make sure your script runs without any errors from top to bottom.

Provided Script

-- --------------------------------------------------------------------------------
-- Class: IT-112
-- Abstract: Review of IT-111
-- --------------------------------------------------------------------------------

-- --------------------------------------------------------------------------------
-- Options
-- --------------------------------------------------------------------------------
USE dbSQL1; -- Get out of the master database
SET NOCOUNT ON; -- Report only errors
-- --------------------------------------------------------------------------------
-- Drop Tables
-- --------------------------------------------------------------------------------
IF OBJECT_ID('TTeamPlayers') IS NOT NULL DROP TABLE TTeamPlayers
IF OBJECT_ID('TPlayers') IS NOT NULL DROP TABLE TPlayers
IF OBJECT_ID('TTeams') IS NOT NULL DROP TABLE TTeams

-- --------------------------------------------------------------------------------
-- Step #1.1: Create Tables
-- --------------------------------------------------------------------------------
CREATE TABLE TTeams
(
intTeamID INTEGER IDENTITY NOT NULL
,strTeam VARCHAR(50) NOT NULL
,strMascot VARCHAR(50) NOT NULL
,CONSTRAINT TTeams_PK PRIMARY KEY ( intTeamID )
)

CREATE TABLE TPlayers
(
intPlayerID INTEGER IDENTITY NOT NULL
,strFirstName VARCHAR(50) NOT NULL
,strLastName VARCHAR(50) NOT NULL
,CONSTRAINT TPlayers_PK PRIMARY KEY ( intPlayerID )
)

CREATE TABLE TTeamPlayers
(
intTeamPlayerID INTEGER IDENTITY NOT NULL
,intTeamID INTEGER NOT NULL
,intPlayerID INTEGER NOT NULL
,CONSTRAINT PlayerTeam_UQ UNIQUE ( intTeamID, intPlayerID )
,CONSTRAINT TTeamPlayers_PK PRIMARY KEY ( intTeamPlayerID )
)

-- --------------------------------------------------------------------------------
-- Step #1.2: Identify and Create Foreign Keys
-- --------------------------------------------------------------------------------
--
-- # Child Parent Column(s)
-- - ----- ------ ---------
-- 1 TTeamPlayers TTeams intTeamID
-- 2 TTeamPlayers TPlayers intPlayerID

-- 1
ALTER TABLE TTeamPlayers ADD CONSTRAINT TTeamPlayers_TTeams_FK
FOREIGN KEY ( intTeamID ) REFERENCES TTeams ( intTeamID )

-- 2
ALTER TABLE TTeamPlayers ADD CONSTRAINT TTeamPlayers_TPlayers_FK
FOREIGN KEY ( intPlayerID ) REFERENCES TPlayers ( intPlayerID )

-- --------------------------------------------------------------------------------
-- Step #1.3: Add at least 3 teams
-- --------------------------------------------------------------------------------
INSERT INTO TTeams ( strTeam, strMascot )
VALUES ( 'Reds', 'Mr. Red' )
,( 'Bengals', 'Bengal Tiger' )
,( 'Duke', 'Blue Devils' )

-- --------------------------------------------------------------------------------
-- Step #1.4: Add at least 3 players
-- --------------------------------------------------------------------------------
INSERT INTO TPlayers ( strFirstName, strLastName )
VALUES ( 'Joey', 'Votto' )
,( 'Joe', 'Morgn' )
,( 'Christian', 'Laettner' )
,( 'Andy', 'Dalton' )

-- --------------------------------------------------------------------------------
-- Step #1.5: Add at at least 6 team/player assignments
-- --------------------------------------------------------------------------------
INSERT INTO TTeamPlayers ( intTeamID, intPlayerID )
VALUES ( 1, 1 )
,( 1, 2 )
,( 2, 4 )
,( 3, 3 )