(Protecting the Integrity of Your Data).sql

CREATE DATABASE ProtectionChapter
go
USE ProtectionChapter
Go
------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - Declarative Data Protection
------------------------------------------------------------------------------------------------------

CREATE SCHEMA Music
GO
CREATE TABLE Music.Artist
(
   ArtistId int NOT NULL,
   Name varchar(60) NOT NULL,

   CONSTRAINT PKNameArtist PRIMARY KEY CLUSTERED (ArtistId),
   CONSTRAINT AKNameArtist_Name UNIQUE NONCLUSTERED (Name)
)
CREATE TABLE Music.Publisher
(
        PublisherId              int primary key,
        Name                      varchar(20),
        CatalogNumberMask varchar(100)
        CONSTRAINT DfltNamePublisher_CatalogNumberMask default ('%'),
        CONSTRAINT AKNamePublisher_Name UNIQUE NONCLUSTERED (Name),
)

CREATE TABLE Music.Album
(
   AlbumId int NOT NULL,
   Name varchar(60) NOT NULL,
   ArtistId int NOT NULL,
   CatalogNumber varchar(20) NOT NULL,
   PublisherId int NOT null --not requiring this information

   CONSTRAINT PKAlbum PRIMARY KEY CLUSTERED(AlbumId),
   CONSTRAINT AKAlbum_Name UNIQUE NONCLUSTERED (Name),
   CONSTRAINT FKMusic_Artist$records$Music_Album
            FOREIGN KEY (ArtistId) REFERENCES Music.Artist(ArtistId),
   CONSTRAINT FKMusic_Publisher$published$Music_Album
            FOREIGN KEY (PublisherId) REFERENCES Music.Publisher(PublisherId)
)
GO

INSERT  INTO Music.Publisher (PublisherId, Name, CatalogNumberMask)
VALUES (1,'Capitol',
        '[0-9][0-9][0-9]-[0-9][0-9][0-9a-z][0-9a-z][0-9a-z]-[0-9][0-9]'),
        (2,'MCA', '[a-z][a-z][0-9][0-9][0-9][0-9][0-9]')

INSERT  INTO Music.Artist(ArtistId, Name)
VALUES (1, 'The Beatles'),(2, 'The Who')

INSERT INTO Music.Album (AlbumId, Name, ArtistId, PublisherId, CatalogNumber)
VALUES (1, 'The White Album',1,1,'433-43ASD-33'),
       (2, 'Revolver',1,1,'111-11111-11'),
       (3, 'Quadrophenia',2,2,'CD12345')
GO

ALTER TABLE Music.Artist WITH CHECK
   ADD CONSTRAINT chkMusic_Artist$Name$NoDuranNames
           CHECK (Name not like '%Duran%')
GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - Declarative Data Protection - [WITH CHECK | WITH NOCHECK]
------------------------------------------------------------------------------------------------------

INSERT INTO Music.Artist(ArtistId, Name)
VALUES (3, 'Duran Duran')
GO

INSERT INTO Music.Artist(ArtistId, Name)
VALUES (3, 'Madonna')
GO

ALTER TABLE Music.Artist WITH NOCHECK
   ADD CONSTRAINT chkMusic_Artist$Name$noMadonnaNames
           CHECK (Name not like '%Madonna%')

Go

UPDATE Music.Artist
SET Name = Name
GO

SELECT CHECK_CLAUSE,
       objectproperty(object_id(CONSTRAINT_SCHEMA + '.' +
                                 CONSTRAINT_NAME),'CnstIsNotTrusted') AS NotTrusted
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'Music'
  And CONSTRAINT_NAME = 'chkMusic_Artist$Name$noMadonnaNames'


------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - Declarative Data Protection - CHECK Constraints Based on Simple Expressions
------------------------------------------------------------------------------------------------------

INSERT INTO Music.Album ( AlbumId, Name, ArtistId, PublisherId, CatalogNumber )
VALUES ( 4, '', 1, 1,'dummy value' )
GO

INSERT INTO Music.Album ( AlbumId, Name, ArtistId, PublisherId, CatalogNumber )
VALUES ( 5, '', 1, 1,'dummy value' )
GO

DELETE FROM Music.Album
WHERE  Name = ''
GO
ALTER TABLE Music.Album WITH CHECK
   ADD CONSTRAINT chkMusic_Album$Name$noEmptyString
           CHECK (LEN(RTRIM(Name)) > 0)

GO
------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - Declarative Data Protection
--  - CHECK Constraints Based on Functions - Example Constraint That Accesses Other Tables (Entry Mask)
------------------------------------------------------------------------------------------------------
CREATE FUNCTION Music.Publisher$CatalogNumberValidate
(
   @CatalogNumber char(12),
   @PublisherId int --now based on the Artist ID
)

RETURNS bit
AS
BEGIN
   DECLARE @LogicalValue bit, @CatalogNumberMask varchar(100)

   SELECT @LogicalValue = CASE WHEN @CatalogNumber LIKE CatalogNumberMask
                                      THEN 1
                               ELSE 0  END
   FROM   Music.Publisher
   WHERE  PublisherId = @PublisherId

   RETURN @LogicalValue
END

GO

SELECT Album.CatalogNumber, Publisher.CatalogNumberMask
FROM   Music.Album as Album
         JOIN Music.Publisher as Publisher
            ON Album.PublisherId = Publisher.PublisherId
GO

ALTER TABLE Music.Album
   WITH CHECK ADD CONSTRAINT
       chkMusic_Album$CatalogNumber$CatalogNumberValidate
       CHECK (Music.Publisher$CatalogNumbervalidate
                          (CatalogNumber,PublisherId) = 1)
GO

--to find where your data is not ready for the constraint,
--you run the following query
SELECT Album.Name, Album.CatalogNumber, Publisher.CatalogNumberMask
FROM Music.Album AS Album
       JOIN Music.Publisher AS Publisher
         on Publisher.PublisherId = Album.PublisherId
WHERE Music.Publisher$CatalogNumbervalidate
                        (Album.CatalogNumber,Album.PublisherId) <> 1
GO
INSERT  Music.Album(AlbumId, Name, ArtistId, PublisherId, CatalogNumber)
VALUES  (4,'who''s next',2,2,'1')
GO
INSERT  Music.Album(AlbumId, Name, ArtistId, CatalogNumber, PublisherId)
VALUES  (4,'who''s next',2,'AC12345',2)

SELECT * FROM Music.Album
GO

SELECT *
FROM   Music.Album AS Album
          JOIN Music.Publisher AS Publisher
                on Publisher.PublisherId = Album.PublisherId
WHERE  Music.Publisher$CatalogNumbervalidate
                        (Album.CatalogNumber,Album.PublisherId) <> 1
GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - Declarative Data Protection
--  - CHECK Constraints Based on Functions - Example Constraint That Accesses Other Rows (Cardinality Enforcement)
------------------------------------------------------------------------------------------------------
CREATE SCHEMA alt
go
CREATE TABLE alt.employee
(
    employeeId    int NOT NULL CONSTRAINT PKalt_employee PRIMARY KEY,
    employeeNumber char(4) NOT NULL
                      CONSTRAINT AKalt_employee_employeeNumber UNIQUE
)
CREATE TABLE alt.office
(
    officeId int NOT NULL CONSTRAINT PKalt_office PRIMARY KEY,
    officeNumber char(4) NOT NULL
                     CONSTRAINT AKalt_office_officeNumber UNIQUE,
)
GO

CREATE TABLE alt.employeeOfficeAssignment
(
       employeeId int,
       officeId  int,
       CONSTRAINT PKalt_employeeOfficeAssignment
                PRIMARY KEY (employeeId, officeId),
       CONSTRAINT FKemployeeOfficeAssignment$assignsAnOfficeTo$employee
                FOREIGN KEY (employeeId) REFERENCES alt.employee(employeeId),
       CONSTRAINT FKemployeeOfficeAssignment$assignsAnOfficeTo$officeId
                FOREIGN KEY (officeId) REFERENCES alt.office(officeId)
)

GO

ALTER TABLE alt.employeeOfficeAssignment
    ADD CONSTRAINT AKemployeeOfficeAssignment_employee UNIQUE (employeeId)
GO

INSERT alt.employee(employeeId, employeeNumber)
VALUES (1,'A001'),
       (2,'A002'),
       (3,'A003')

INSERT INTO alt.office(officeId,officeNumber)
VALUES (1,'3001'),
       (2,'3002'),
       (3,'3003')
GO

CREATE FUNCTION alt.employeeOfficeAssignment$officeEmployeeCount
( @officeId int)
RETURNS int AS
 BEGIN
    RETURN (SELECT count(*)
            FROM   alt.employeeOfficeAssignment
            WHERE  officeId = @officeId
            )
  END
GO

ALTER TABLE alt.employeeOfficeAssignment
    ADD CONSTRAINT CHKalt_employeeOfficeAssignment_employeesInOfficeTwoOrLess
         CHECK (alt.employeeOfficeAssignment$officeEmployeeCount(officeId) <= 2)
GO

INSERT alt.employeeOfficeAssignment(officeId, employeeId)
VALUES (1,1)
GO
INSERT alt.employeeOfficeAssignment(officeId, employeeId)
VALUES (1,2)
GO

INSERT alt.employeeOfficeAssignment(officeId, employeeId)
VALUES (1,3)
GO
INSERT alt.employeeOfficeAssignment(officeId, employeeId)
VALUES (2,3)
GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - Declarative Data Protection
--  - CHECK Constraints Based on Functions - Errors Caused by Constraints
------------------------------------------------------------------------------------------------------

CREATE SCHEMA utility
CREATE TABLE utility.ErrorMap
(
    ConstraintName sysname primary key,
    Message         varchar(2000)
)
go
INSERT utility.ErrorMap(constraintName, message)
VALUES ('chkMusic_Album$CatalogNumber$CatalogNumberValidate',
        'The catalog number does not match the format set up by the Publisher')
GO

CREATE PROCEDURE utility.ErrorMap$MapError
(
    @ErrorNumber  int = NULL,
    @ErrorMessage nvarchar(2000) = NULL,
    @ErrorSeverity INT= NULL,
    @ErrorState INT = NULL
) AS
  BEGIN
    --use values in ERROR_ functions unless the user passes in values
    SET @ErrorNumber = Coalesce(@ErrorNumber, ERROR_NUMBER())
    SET @ErrorMessage = Coalesce(@ErrorMessage, ERROR_MESSAGE())
    SET @ErrorSeverity = Coalesce(@ErrorSeverity, ERROR_SEVERITY())
    SET @ErrorState = Coalesce(@ErrorState,ERROR_STATE())

    DECLARE @originalMessage nvarchar(2000)
    SET @originalMessage = ERROR_MESSAGE()


    IF @ErrorNumber = 547
      BEGIN
        SET @ErrorMessage =
                        (SELECT message
                         FROM   utility.ErrorMap
                         WHERE  constraintName =
         --this substring pulls the constraint name from the message
         substring( @ErrorMessage,CHARINDEX('constraint "',@ErrorMessage) + 12,
                             charindex('"',substring(@ErrorMessage,
                             CHARINDEX('constraint "',@ErrorMessage) +
                                                                12,2000))-1)
                            )      END
    ELSE
        SET @ErrorMessage = @ErrorMessage

    SET @ErrorState = CASE when @ErrorState = 0 THEN 1 ELSE @ErrorState END

    --if the error was not found, get the original message
    SET @ErrorMessage = isNull(@ErrorMessage, @originalMessage)
    RAISERROR (@ErrorMessage, @ErrorSeverity,@ErrorState )
  END
GO
BEGIN TRY
     INSERT  Music.Album(AlbumId, Name, ArtistId, CatalogNumber, PublisherId)
     VALUES  (5,'who are you',2,'badnumber',2)
END TRY
BEGIN CATCH
    EXEC utility.ErrorMap$MapError
END CATCH
GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - AFTER Triggers
------------------------------------------------------------------------------------------------------
/*
CREATE TRIGGER <schema>.<tablename>$<actions>[<purpose>]Trigger
ON <schema>.<tablename>
AFTER <comma delimited actions> AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET NOCOUNT ON --to avoid the rowcount messages
   SET ROWCOUNT 0 --in case the client has modified the rowcount

   BEGIN TRY
          --[validation section]
          --[modification section]
   END TRY
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE nvarchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
*/
CREATE TABLE utility.ErrorLog(
        ERROR_NUMBER int NOT NULL,
        ERROR_LOCATION sysname NOT NULL,
        ERROR_MESSAGE varchar(4000),
        ERROR_DATE datetime NULL
              CONSTRAINT dfltErrorLog_error_date  DEFAULT (getdate()),
        ERROR_USER sysname NOT NULL
              --use original_login to capture the user name of the actual user
              --not a user they have impersonated
              CONSTRAINT dfltErrorLog_error_user_name DEFAULT (original_login())
)
GO
CREATE PROCEDURE utility.ErrorLog$insert
(
        @ERROR_NUMBER int = NULL,
        @ERROR_LOCATION sysname = NULL,
        @ERROR_MESSAGE varchar(4000) = NULL
) as
 BEGIN
        BEGIN TRY
           INSERT INTO utility.ErrorLog(ERROR_NUMBER,
                                         ERROR_LOCATION, ERROR_MESSAGE)
           SELECT isnull(@ERROR_NUMBER,ERROR_NUMBER()),
                  isnull(@ERROR_LOCATION,ERROR_MESSAGE()),
                  isnull(@ERROR_MESSAGE,ERROR_MESSAGE())
        END TRY
        BEGIN CATCH
           INSERT INTO utility.ErrorLog(ERROR_NUMBER,
                                         ERROR_LOCATION, ERROR_MESSAGE)
           VALUES (-100, 'utility.ErrorLog$insert',
                        'An invalid call was made to the error log procedure')
        END CATCH
END
GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - AFTER Triggers
--   - Range Checks on Multiple Rows
------------------------------------------------------------------------------------------------------
CREATE SCHEMA Accounting
GO
CREATE TABLE Accounting.Account
(
        AccountNumber        char(10)
                  constraint PKAccounting_Account primary key
        --would have other columns
)

CREATE TABLE Accounting.AccountActivity
(
        AccountNumber                char(10)
            constraint Accounting_Account$has$Accounting_AccountActivity
                       foreign key references Accounting.Account(AccountNumber),
       --this might be a value that each ATM/Teller generates
        TransactionNumber            char(20),
        Date                         datetime,
        TransactionAmount            numeric(12,2),
        constraint PKAccounting_AccountActivity
                      PRIMARY KEY (AccountNumber, TransactionNumber)
)
GO

CREATE TRIGGER Accounting.AccountActivity$insertUpdateTrigger
ON Accounting.AccountActivity
AFTER INSERT,UPDATE AS
BEGIN
   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET NOCOUNT ON
   SET ROWCOUNT 0 --in case the client has modified the rowcount

   BEGIN TRY

   --[validation section]
   --disallow Transactions that would put balance into negatives
   IF EXISTS ( SELECT AccountNumber
               FROM Accounting.AccountActivity as AccountActivity
               WHERE EXISTS (SELECT *
                             FROM   inserted
                             WHERE  inserted.AccountNumber =
                               AccountActivity.AccountNumber)
                   GROUP BY AccountNumber
                   HAVING sum(TransactionAmount) < 0)
      BEGIN
         IF @rowsAffected = 1
             SELECT @msg = 'Account: ' + AccountNumber +
                  ' TransactionNumber:' +
                   cast(TransactionNumber as varchar(36)) +
                   ' for amount: ' + cast(TransactionAmount as varchar(10))+
                   ' cannot be processed as it will cause a negative balance'
             FROM   inserted
        ELSE
          SELECT @msg = 'One of the rows caused a negative balance'
         RAISERROR (@msg, 16, 1)
      END

   --[modification section]
   END TRY
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE varchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
GO

--create some set up test data
INSERT into Accounting.Account(AccountNumber)
VALUES ('1111111111')

GO
INSERT  into Accounting.AccountActivity(AccountNumber, TransactionNumber,
                                         Date, TransactionAmount)
VALUES ('1111111111','A0000000000000000001','20050712',100),
 ('1111111111','A0000000000000000002','20050713',100)
GO
INSERT  into Accounting.AccountActivity(AccountNumber, TransactionNumber,
                                         Date, TransactionAmount)
VALUES ('1111111111','A0000000000000000003','20050713',-300)
GO

--create new Account
INSERT  into Accounting.Account(AccountNumber)
VALUES ('2222222222')
GO
--Now, this data will violate the constraint for the new Account:
INSERT  into Accounting.AccountActivity(AccountNumber, TransactionNumber,
                                        Date, TransactionAmount)
VALUES ('1111111111','A0000000000000000004','20050714',100),
       ('2222222222','A0000000000000000005','20050715',100),
       ('2222222222','A0000000000000000006','20050715',100),
       ('2222222222','A0000000000000000007','20050715',-201)

GO

--Viewing trigger events

SELECT sys.trigger_events.type_desc
FROM sys.trigger_events
         JOIN sys.triggers
                  ON sys.triggers.object_id = sys.trigger_events.object_id
WHERE sys.triggers.name = 'AccountActivity$insertUpdateTrigger'

GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - AFTER Triggers
--   - Maintaining Summary Values
------------------------------------------------------------------------------------------------------
ALTER TABLE Accounting.Account
   ADD Balance numeric(12,2)
      CONSTRAINT DfltAccounting_Account_Balance DEFAULT(0.00)

GO
SELECT  Account.AccountNumber,
        SUM(coalesce(TransactionAmount,0.00)) AS NewBalance
FROM   Accounting.Account
        LEFT OUTER JOIN Accounting.AccountActivity
            ON Account.AccountNumber = AccountActivity.AccountNumber
GROUP  BY Account.AccountNumber
GO

WITH  Updater as (
SELECT  Account.AccountNumber,
        SUM(coalesce(TransactionAmount,0.00)) as NewBalance
FROM   Accounting.Account
        LEFT OUTER JOIN Accounting.AccountActivity
            On Account.AccountNumber = AccountActivity.AccountNumber
GROUP  BY Account.AccountNumber, Account.Balance)
UPDATE Account
SET    Balance = Updater.NewBalance
FROM   Accounting.Account
         JOIN Updater
                on Account.AccountNumber = Updater.AccountNumber
GO

ALTER TRIGGER Accounting.AccountActivity$insertUpdateTrigger
ON Accounting.AccountActivity
AFTER INSERT,UPDATE AS
BEGIN
   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET NOCOUNT ON
   SET ROWCOUNT 0 --in case the client has modified the rowcount

   BEGIN TRY

   --[validation section]
   --disallow Transactions that would put balance into negatives
   IF EXISTS ( SELECT AccountNumber
               FROM Accounting.AccountActivity as AccountActivity
               WHERE EXISTS (SELECT *
                             FROM   inserted
                             WHERE  inserted.AccountNumber =
                               AccountActivity.AccountNumber)
                   GROUP BY AccountNumber
                   HAVING sum(TransactionAmount) < 0)
      BEGIN
         IF @rowsAffected = 1
             SELECT @msg = 'Account: ' + AccountNumber +
                  ' TransactionNumber:' +
                   cast(TransactionNumber as varchar(36)) +
                   ' for amount: ' + cast(TransactionAmount as varchar(10))+
                   ' cannot be processed as it will cause a negative balance'
             FROM   inserted
        ELSE
          SELECT @msg = 'One of the rows caused a negative balance'
         RAISERROR (@msg, 16, 1)
      END

    --[modification section]
    IF UPDATE (TransactionAmount)
        WITH  Updater as (
        SELECT  Account.AccountNumber,
                SUM(coalesce(TransactionAmount,0.00)) as NewBalance
        FROM   Accounting.Account
                LEFT OUTER JOIN Accounting.AccountActivity
                    On Account.AccountNumber = AccountActivity.AccountNumber
               --This where clause limits the summarizations to those rows
               --that were modified by the DML statement that caused
               --this trigger to fire.
        WHERE  EXISTS (SELECT *
                       FROM   Inserted
                       WHERE  Account.AccountNumber = Inserted.AccountNumber)
        GROUP  BY Account.AccountNumber, Account.Balance)
        UPDATE Account
        SET    Balance = Updater.NewBalance
        FROM   Accounting.Account
                  JOIN Updater
                      on Account.AccountNumber = Updater.AccountNumber

   END TRY
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE varchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
GO
INSERT  into Accounting.AccountActivity(AccountNumber, TransactionNumber,
                                        Date, TransactionAmount)
VALUES ('1111111111','A0000000000000000004','20050714',100)
GO

SELECT  Account.AccountNumber,
        SUM(coalesce(TransactionAmount,0.00)) AS NewBalance
FROM   Accounting.Account
        LEFT OUTER JOIN Accounting.AccountActivity
            ON Account.AccountNumber = AccountActivity.AccountNumber
GROUP  BY Account.AccountNumber
GO

INSERT  into Accounting.AccountActivity(AccountNumber, TransactionNumber,
                                        Date, TransactionAmount)
VALUES ('1111111111','A0000000000000000005','20050714',100),
       ('2222222222','A0000000000000000006','20050715',100),
       ('2222222222','A0000000000000000007','20050715',100)
GO

SELECT  Account.AccountNumber,
        SUM(coalesce(TransactionAmount,0.00)) AS NewBalance
FROM   Accounting.Account
        LEFT OUTER JOIN Accounting.AccountActivity
            ON Account.AccountNumber = AccountActivity.AccountNumber
GROUP  BY Account.AccountNumber
GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - AFTER Triggers
--   - Cascading Inserts
------------------------------------------------------------------------------------------------------

CREATE SCHEMA Internet
go
CREATE TABLE Internet.Url
(
    UrlId int not null identity(1,1) constraint PKUrl primary key,
    Name  varchar(60) not null constraint AKInternet_Url_Name UNIQUE,
    Url   varchar(200) not null constraint AKInternet_Url_Url UNIQUE
)

--Not a user manageable table, so not using identity key (as discussed in
--Chapter 5 when I discussed choosing keys) in this one table.  Others are
--using identity-based keys in this example
CREATE TABLE Internet.UrlStatusType
(
        UrlStatusTypeId  int not null
                      CONSTRAINT PKInternet_UrlStatusType PRIMARY KEY,
        Name varchar(20) NOT NULL
                      CONSTRAINT AKInternet_UrlStatusType UNIQUE,
        DefaultFlag bit NOT NULL,
        DisplayOnSiteFlag bit NOT NULL
)

CREATE TABLE Internet.UrlStatus
(
        UrlStatusId int not null identity(1,1)
                      CONSTRAINT PKInternet_UrlStatus PRIMARY KEY,
        UrlStatusTypeId int NOT NULL
                      CONSTRAINT
               Internet_UrlStatusType$defines_status_type_of$Internet_UrlStatus
                      REFERENCES Internet.UrlStatusType(UrlStatusTypeId),
        UrlId int NOT NULL
          CONSTRAINT Internet_Url$has_status_history_in$Internet_UrlStatus
                      REFERENCES Internet.Url(UrlId),
        ActiveTime        datetime,
        CONSTRAINT AKInternet_UrlStatus_statusUrlDate
                      UNIQUE (UrlStatusTypeId, UrlId, ActiveTime)
)
--set up status types
INSERT  Internet.UrlStatusType (UrlStatusTypeId, Name,
                                   DefaultFlag, DisplayOnSiteFlag)
VALUES (1, 'Unverified',1,0),
       (2, 'Verified',0,1),
       (3, 'Unable to locate',0,0)
GO

CREATE TRIGGER Internet.Url$afterInsertTrigger
ON Internet.Url
AFTER INSERT AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET NOCOUNT ON --to avoid the rowcount messages
   SET ROWCOUNT 0 --in case the client has modified the rowcount

   BEGIN TRY
          --[validation section]

           --[modification section]

          --add a row to the UrlStatus table to tell it that the new row
          --should start out as the default status
          INSERT INTO Internet.UrlStatus (UrlId, UrlStatusTypeId, ActiveTime)
          SELECT INSERTED.UrlId, UrlStatusType.UrlStatusTypeId,
                  current_timestamp
          FROM INSERTED
                CROSS JOIN (SELECT UrlStatusTypeId
                            FROM   UrlStatusType
                            WHERE  DefaultFlag = 1)  as UrlStatusType
                                           --use cross join with a WHERE clause
                                           --as this is not technically a join
                                           --between INSERTED and UrlType
   END TRY
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              --or this will not get rolled back
              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE varchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
GO
INSERT  into Internet.Url(Name, Url)
VALUES ('More info can be found here',
        'http://sqlblog.com/blogs/louis_davidson/default.aspx')

SELECT * FROM Internet.Url
SELECT * FROM Internet.UrlStatus

GO
------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - AFTER Triggers
--   - Cascading from Child to Parent
------------------------------------------------------------------------------------------------------
--start a schema for entertainment-related tables
CREATE SCHEMA Entertainment
go
CREATE TABLE Entertainment.GamePlatform
(
    GamePlatformId int CONSTRAINT PKGamePlatform PRIMARY KEY,
    Name  varchar(20) CONSTRAINT AKGamePlatform_Name UNIQUE
)
CREATE TABLE Entertainment.Game
(
    GameId  int CONSTRAINT PKGame PRIMARY KEY,
    Name    varchar(20) CONSTRAINT AKGame_Name UNIQUE
    --more details that are common to all platforms
)

--associative entity with cascade relationships back to Game and GamePlatform
CREATE TABLE Entertainment.GameInstance
(
    GamePlatformId int,
    GameId int,
    PurchaseDate date,
    CONSTRAINT PKGameInstance PRIMARY KEY (GamePlatformId, GameId),
    CONSTRAINT
    Entertainment_Game$is_owned_on_platform_by$Entertainment_GameInstance
      FOREIGN KEY (GameId)REFERENCES Entertainment.Game(GameId)
                                               ON DELETE CASCADE,
      CONSTRAINT
        Entertainment_GamePlatform$is_linked_to$Entertainment_GameInstance
      FOREIGN KEY (GamePlatformId)
           REFERENCES Entertainment.GamePlatform(GamePlatformId)
                ON DELETE CASCADE
)
GO
INSERT  into Entertainment.Game (GameId, Name)
VALUES (1,'Super Mario Bros'),
       (2,'Legend Of Zelda')

INSERT  into Entertainment.GamePlatform(GamePlatformId, Name)
VALUES (1,'Nintendo Wii'),   --Yes, as a matter of fact I am a
       (2,'Nintendo DS')     --Nintendo Fanboy, why do you ask?

INSERT  into Entertainment.GameInstance(GamePlatformId, GameId, PurchaseDate)
VALUES (1,1,'20060404'),
       (1,2,'20070510'),
       (2,2,'20070404')

--the full outer joins ensure that all rows are returned from all sets, leaving
--nulls where data is missing
SELECT  GamePlatform.Name as Platform, Game.Name as Game, GameInstance. PurchaseDate
FROM    Entertainment.Game as Game
            full outer join Entertainment.GameInstance as GameInstance
                    on Game.GameId = GameInstance.GameId
            full outer join Entertainment.GamePlatform
                    on GamePlatform.GamePlatformId = GameInstance.GamePlatformId

GO

CREATE TRIGGER Entertainment.GameInstance$afterDeleteTrigger
ON Entertainment.GameInstance
AFTER delete AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET NOCOUNT ON --to avoid the rowcount messages
   SET ROWCOUNT 0 --in case the client has modified the rowcount

   BEGIN TRY
        --[validation section]

        --[modification section]
        --delete all Games
        DELETE Game       --where the GameInstance was delete
        WHERE  GameId in (SELECT deleted.GameId
                          FROM   deleted     --and there are no GameInstances
                           WHERE  not exists (SELECT  *        --left
                                              FROM    GameInstance
                                              WHERE   GameInstance.GameId =
                                                               deleted.GameId))
   END TRY
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE varchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
GO
DELETE  Entertainment.GamePlatform
WHERE   GamePlatformId = 1
go
SELECT  GamePlatform.Name as platform, Game.Name as Game, GameInstance. PurchaseDate
FROM    Entertainment.Game as Game
            FULL OUTER JOIN Entertainment.GameInstance as GameInstance
                    on Game.GameId = GameInstance.GameId
            FULL OUTER JOIN Entertainment.GamePlatform
                    on GamePlatform.GamePlatformId = GameInstance.GamePlatformId
GO




------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - AFTER Triggers
--   - Maintaining an Audit Trail
------------------------------------------------------------------------------------------------------
CREATE SCHEMA hr
go
CREATE TABLE hr.employee
(
    employee_id char(6) CONSTRAINT PKhr_employee PRIMARY KEY,
    first_name  varchar(20),
    last_name   varchar(20),
    salary      money
)
CREATE TABLE hr.employee_auditTrail
(
    employee_id          char(6),
    date_changed         datetime not null --default so we don't have to
                                           --code for it
          CONSTRAINT DfltHr_employee_date_changed DEFAULT (current_timestamp),
    first_name           varchar(20),
    last_name            varchar(20),
    salary               decimal(12,2),
    --the following are the added columns to the original
    --structure of hr.employee
    action               char(6)
          CONSTRAINT ChkHr_employee_action --we don't log inserts, only changes
                                          CHECK(action in ('delete','update')),
    changed_by_user_name sysname
                CONSTRAINT DfltHr_employee_changed_by_user_name
                                          DEFAULT (original_login()),
    CONSTRAINT PKemployee_auditTrail PRIMARY KEY (employee_id, date_changed)
)
GO

CREATE TRIGGER hr.employee$insertAndDeleteAuditTrailTrigger
ON hr.employee
AFTER UPDATE, DELETE AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET NOCOUNT ON --to avoid the rowcount messages
   SET ROWCOUNT 0 --in case the client has modified the rowcount
   BEGIN TRY
          --[validation section]
          --[modification section]
          --since we are only doing update and delete, we just
          --need to see if there are any rows
          --inserted to determine what action is being done.
          DECLARE @action char(6)
          SET @action = case when (SELECT count(*) from inserted) > 0
                        then 'update' else 'delete' end

          --since the deleted table contains all changes, we just insert all
          --of the rows in the deleted table and we are done.
          INSERT employee_auditTrail (employee_id, first_name, last_name,
                                     salary, action)
          SELECT employee_id, first_name, last_name, salary, @action
          FROM   deleted

   END TRY
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE varchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
GO
INSERT hr.employee (employee_id, first_name, last_name, salary)
VALUES (1, ' Phillip','Taibul',10000)
GO
UPDATE hr.employee
SET salary = salary * 1.10 --ten percent raise!
WHERE employee_id = 1

SELECT *
FROM   hr.employee
GO
SELECT *
FROM   hr.employee_auditTrail
GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - INSTEAD OF Triggers
------------------------------------------------------------------------------------------------------

/*
CREATE TRIGGER <schema>.<tablename>$InsteadOf<actions>[<purpose>]Trigger
ON <schema>.<tablename>
INSTEAD OF <comma delimited actions> AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET NOCOUNT ON --to avoid the rowcount messages
   SET ROWCOUNT 0 --in case the client has modified the rowcount

   BEGIN TRY
          --[validation section]
          --[modification section]
          --<perform action>
   END TRY
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE nvarchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
*/
------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - INSTEAD OF Triggers
--   - Automatically Maintaining Columns
------------------------------------------------------------------------------------------------------
CREATE SCHEMA school
Go
CREATE TABLE school.student
(
      studentId       int identity not null
            CONSTRAINT PKschool_student PRIMARY KEY,
      studentIdNumber char(8) not null
            CONSTRAINT AKschool_student_studentIdNumber UNIQUE,
      firstName       varchar(20) not null,
      lastName        varchar(20) not null,
--Note that we add these columns to the implementation model, not to the logical
--model. These columns do not actually refer to the student being modeled, they are
--required simply to help with programming and tracking.
      rowCreateDate   datetime not null
            CONSTRAINT dfltSchool_student_rowCreateDate
                                 DEFAULT (current_timestamp),
      rowCreateUser   sysname not null
            CONSTRAINT dfltSchool_student_rowCreateUser DEFAULT (current_user)
)
GO

CREATE TRIGGER school.student$insteadOfInsert
ON school.student
INSTEAD OF INSERT AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET ROWCOUNT 0 --in case the client has modified the rowcount
   SET NOCOUNT ON --to avoid the rowcount messages

   BEGIN TRY
          --[validation section]
          --[modification section]
          --<perform action>
          INSERT INTO school.student(studentIdNumber, firstName, lastName,
                                     rowCreateDate, rowCreateUser)
          SELECT studentIdNumber, firstName, lastName,
                                        current_timestamp, suser_sname()
          FROM  inserted   --no matter what the user put in the inserted row
   END TRY         --when the row was created, these values will be inserted
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE nvarchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
GO
INSERT  into school.student(studentIdNumber, firstName, lastName)
VALUES ( '0000001',' Gray', ' Tezine' )

GO
SELECT * FROM school.student
GO
INSERT  school.student(studentIdNumber, firstName, lastName, rowCreateDate,
                       rowCreateUser)
VALUES ( '000002','Norm', 'Ull','99990101','some user' )
GO
SELECT * FROM school.student
GO

------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - INSTEAD OF Triggers
--   - Formatting User Input
------------------------------------------------------------------------------------------------------
CREATE FUNCTION Utility.TitleCase
(
   @inputString varchar(2000)
)
RETURNS varchar(2000) AS
BEGIN
   -- set the whole string to lower
   SET @inputString = LOWER(@inputstring)
   -- then use stuff to replace the first character
   SET @inputString =
   --STUFF in the uppercased character in to the next character,
   --replacing the lowercased letter
   STUFF(@inputString,1,1,UPPER(SUBSTRING(@inputString,1,1)))

   --@i is for the loop counter, initialized to 2
   DECLARE @i int
   SET @i = 1

   --loop from the second character to the end of the string
   WHILE @i < LEN(@inputString)
   BEGIN
      --if the character is a space
      IF SUBSTRING(@inputString,@i,1) = ' '
      BEGIN
         --STUFF in the uppercased character into the next character
         SET @inputString = STUFF(@inputString,@i +
         1,1,UPPER(SUBSTRING(@inputString,@i + 1,1)))
      END
      --increment the loop counter
      SET @i = @i + 1
   END
   RETURN @inputString
END
GO

ALTER TRIGGER school.student$insteadOfInsert
ON school.student
INSTEAD OF INSERT AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET ROWCOUNT 0 --in case the client has modified the rowcount
   SET NOCOUNT ON --to avoid the rowcount messages

   BEGIN TRY
          --[validation section]
          --[modification section]
          --<perform action>
          INSERT INTO school.student(studentIdNumber, firstName, lastName,
                                     rowCreateDate, rowCreateUser)

          SELECT studentIdNumber,
                 Utility.titleCase(firstName),
                 Utility.titleCase(lastName),
                 current_timestamp, suser_sname()
          FROM  inserted   --no matter what the user put in the inserted row
   END TRY                 --when the row was created, these values will be inserted
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE nvarchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
GO

INSERT school.student(studentIdNumber, firstName, lastName)
VALUES ( '0000007','CaPtain', 'von nuLLY')
GO

SELECT *
FROM school.student

GO
------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - INSTEAD OF Triggers
--   - Redirecting Invalid Data to an Exception Table
------------------------------------------------------------------------------------------------------

CREATE SCHEMA Measurements
go
CREATE TABLE Measurements.WeatherReading
(
    WeatherReadingId int identity
          CONSTRAINT PKWeatherReading PRIMARY KEY,
    ReadingTime   datetime
          CONSTRAINT AKMeasurements_WeatherReading_Date UNIQUE,
    Temperature     float
          CONSTRAINT chkMeasurements_WeatherReading_Temperature
                      CHECK(Temperature between -80 and 150)
                      --raised from last edition for global warming
)
GO
INSERT  into Measurements.WeatherReading (ReadingTime, Temperature)
VALUES ('20080101 0:00',82.00), ('20080101 0:01',89.22),
       ('20080101 0:02',600.32),('20080101 0:03',88.22),
       ('20080101 0:04',99.01)
GO

CREATE TABLE Measurements.WeatherReading_exception
(
    WeatherReadingId  int identity
          CONSTRAINT PKMeasurements_WeatherReading_exception PRIMARY KEY,
    ReadingTime       datetime,
    Temperature       float
)
GO

CREATE TRIGGER Measurements.WeatherReading$InsteadOfInsertTrigger
ON Measurements.WeatherReading
INSTEAD OF INSERT AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount

   --no need to continue on if no rows affected
   IF @rowsAffected = 0 return

   SET NOCOUNT ON --to avoid the rowcount messages
   SET ROWCOUNT 0 --in case the client has modified the rowcount

   BEGIN TRY
          --[validation section]
          --[modification section]

          --<perform action>

           --BAD data
          INSERT Measurements.WeatherReading_exception
                                     (ReadingTime, Temperature)
          SELECT ReadingTime, Temperature
          FROM   inserted
          WHERE  NOT(Temperature between -80 and 120)

           --GOOD data
          INSERT Measurements.WeatherReading (ReadingTime, Temperature)
          SELECT ReadingTime, Temperature
          FROM   inserted
          WHERE  (Temperature between -80 and 120)
   END TRY
   BEGIN CATCH
              IF @@trancount > 0
                  ROLLBACK TRANSACTION

              EXECUTE utility.ErrorLog$insert

              DECLARE @ERROR_MESSAGE nvarchar(4000)
              SET @ERROR_MESSAGE = ERROR_MESSAGE()
              RAISERROR (@ERROR_MESSAGE,16,1)

     END CATCH
END
GO

INSERT  into Measurements.WeatherReading (ReadingTime, Temperature)
VALUES ('20080101 0:00',82.00), ('20080101 0:01',89.22),
       ('20080101 0:02',600.32),('20080101 0:03',88.22),
       ('20080101 0:04',99.01)

SELECT *
FROM Measurements.WeatherReading
GO

SELECT *
FROM   Measurements.WeatherReading_exception
GO



------------------------------------------------------------------------------------------------------
-- Automatic Data Protection - DML Triggers - INSTEAD OF Triggers
--   - Forcing No Action to Be Performed on a Table
------------------------------------------------------------------------------------------------------

CREATE SCHEMA System
go
CREATE TABLE System.Version
(
    DatabaseVersion varchar(10)
)
INSERT  into System.Version (DatabaseVersion)
VALUES ('1.0.12')
GO

CREATE TRIGGER System.Version$InsteadOfInsertUpdateDeleteTrigger
ON System.Version
INSTEAD OF INSERT, UPDATE, DELETE AS
BEGIN

   DECLARE @rowsAffected int,    --stores the number of rows affected
           @msg varchar(2000)    --used to hold the error message

   SET @rowsAffected = @@rowcount
   --no need to complain if no rows affected
   IF @rowsAffected = 0 return

   --No error handling necessary, just the message.
   --We just put the kibosh on the action.
   RAISERROR
      ('The System.Version table may not be modified in production',
        16,1)
END

GO

delete system.version
GO
ALTER TABLE system.version
    DISABLE TRIGGER version$InsteadOfInsertUpdateDelete
Go
--------------------------------------------------------------------------------------------------
-- Handing Errors from Triggers and Constraints
--------------------------------------------------------------------------------------------------
CREATE TABLE alt.errorHandlingTest
(
    errorHandlingTestId   int CONSTRAINT PKerrorHandlingTest PRIMARY KEY,
    CONSTRAINT ChkAlt_errorHandlingTest_errorHandlingTestId_greaterThanZero
           CHECK (errorHandlingTestId > 0)
)
GO

CREATE TRIGGER alt.errorHandlingTest$afterInsertTrigger
ON alt.errorHandlingTest
AFTER INSERT
AS

    RAISERROR ('Test Error',16,1)
    ROLLBACK TRANSACTION
GO

--NO Transaction, Constraint Error
INSERT alt.errorHandlingTest
VALUES (-1)
SELECT 'continues'
GO

INSERT alt.errorHandlingTest
VALUES (1)
SELECT 'continues'
GO

BEGIN TRANSACTION
BEGIN TRY
    INSERT alt.errorHandlingTest
    VALUES (-1)
    COMMIT
END TRY
BEGIN CATCH
    SELECT  CASE XACT_STATE()
                WHEN 1 THEN 'Committable'
                WHEN 0 THEN 'No transaction'
                ELSE 'Uncommitable tran' END as XACT_STATE
            ,ERROR_NUMBER() AS ErrorNumber
            ,ERROR_MESSAGE() as ErrorMessage
    ROLLBACK TRANSACTION
END CATCH
GO

BEGIN TRANSACTION
BEGIN TRY
    INSERT alt.errorHandlingTest
    VALUES (1)
    COMMIT
END TRY
BEGIN CATCH
    SELECT  CASE XACT_STATE()
                WHEN 1 THEN 'Committable'
                WHEN 0 THEN 'No transaction'
                ELSE 'Uncommitable tran' END as XACT_STATE
            ,ERROR_NUMBER() AS ErrorNumber
            ,ERROR_MESSAGE() as ErrorMessage
    ROLLBACK TRANSACTION
END CATCH
GO


BEGIN TRY
    DECLARE @errorMessage nvarchar(4000)
    SET @errorMessage = 'Error inserting data into alt.errorHandlingTest'
    INSERT alt.errorHandlingTest
    VALUES (1)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    --I also add in the stored procedure or trigger where the error
    --occurred also when in a coded object
    SET @errorMessage = Coalesce(@errorMessage,'') +
          ' ( System Error: ' + CAST(ERROR_NUMBER() as varchar(10)) +
          ':' + ERROR_MESSAGE() + ': Line Number:' +
          CAST(ERROR_LINE() as varchar(10)) + ')'
    RAISERROR (@errorMessage,16,1)
END CATCH