-- Kolo Bank: Data Definition Language (DDL) --

-- =============================================
-- Database:     KoloBank1
-- Author:       FunctionX
-- Date Created: Sunday 22 December 2010
-- Updated:      Sunday 25 December 2012
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'KoloBank1'
)
DROP DATABASE KoloBank1;
GO
CREATE DATABASE KoloBank1;
GO

USE KoloBank1;
GO

CREATE SCHEMA Management;
GO

-- ===================================================
-- Table:	Management.DatabaseOperations
-- Description: This table is used to hold information
--              about operations performed on any table
--              of the database. It specifies:
--              a. The type of object on which the action
--                 was performed. The types of object can
--                 be a table
--              b. The name of the table
--              c. The name of the employee who 
--                 performed the action.
--              d. The action that was performed. This
--                 can be an insert, an update, or a 
--                 delete operation
--              e. The date/time the action was performed
-- ===================================================
CREATE TABLE Management.DatabaseOperations
(
    OperationID     int identity(1,1) NOT NULL,
    ObjectType      nchar(20) default N'Table',
    ObjectName      nvarchar(40),
    PerformedBy     nvarchar(50),
    ActionPerformed nvarchar(max),
    DatePerformed   date,
	TimePerformed   time,
    CONSTRAINT PK_Operations PRIMARY KEY(OperationID)
);
GO

CREATE TRIGGER LogNewTableCreation
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    INSERT INTO Management.DatabaseOperations(ObjectType, PerformedBy, ActionPerformed, DatePerformed, TimePerformed)
    VALUES(default, SUSER_SNAME(), N'A new table was created', SYSDATETIME(), SYSDATETIME())
END
GO