-- Remove the existing testing table from a previous test
if OBJECT_ID('TriggerTester') is NOT NULL  drop table TriggerTester
SET NOCOUNT ON
PRINT 'Trigger Testing Script'
GO

-- Create the TriggerTester Table
CREATE TABLE TriggerTester (
          [ID]        int IDENTITY (1,1)  PRIMARY KEY
        , RowDesc     VARCHAR(32) NOT NULL
        , LastUpdated datetime NULL
             )
PRINT 'Table created'
GO

-- Create the Update trigger 
CREATE TRIGGER TriggerTester_Update_demo on TriggerTester 
          FOR UPDATE
AS

    UPDATE TriggerTester
         SET LastUpdated = GETDATE()
       FROM TriggerTester 
           inner join inserted
               on inserted.[ID] = TriggerTester.[ID]
GO
       
-- Insert on row into the table and verify that LastUpdate is NULL
PRINT 'Inserting one row into TriggerTester'
INSERT INTO TriggerTester (RowDesc) VALUES ('Test Row')
SELECT *, getdate() NOW FROM TriggerTester
GO

-- Update and verify that the trigger has run, because LastUpdate shoould
-- be non-Null and the same as NOW
UPDATE TriggerTester  SET RowDesc = 'Test Row Updated'
SELECT *, getdate() NOW FROM TriggerTester
GO

-- Show the staus of all triggers on the TriggerTester table
SELECT * FROM udf_Tbl_TriggerStatusTAB ('TriggerTester')
GO

-- Disable the Trigger
ALTER TABLE TriggerTester DISABLE TRIGGER TriggerTester_Update_demo
SELECT *  FROM udf_Tbl_TriggerStatusTAB ('TriggerTester%')
GO

-- Perform the update and see that LastUpdated and NOW are different
-- because the trigger was not run
UPDATE TriggerTester SET RowDesc = 'Test Row Updated Again'
SELECT *, getdate() NOW FROM TriggerTester
GO

-- Enable the trigger
ALTER TABLE TriggerTester ENABLE TRIGGER TriggerTester_Update_demo
SELECT * FROM udf_Tbl_TriggerStatusTAB ('TriggerTester')
GO