---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第9章 トリガ
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 9.1 DMLトリガ
---------------------------------------------------------------------

CREATE TABLE dbo.MyEquipment(
    Id INT IDENTITY,
    Description VARCHAR(500)
)
GO

-- テーブルにレコードを格納する
INSERT dbo.MyEquipment(Description)
    SELECT TOP 5 Make + ' ' + Model FROM dbo.Equipment
GO

CREATE TRIGGER dbo.trMyEquipment_D
ON dbo.MyEquipment
AFTER DELETE -- DELETE後の処理
AS
    PRINT CAST(@@ROWCOUNT AS NVARCHAR)
        + N'件のレコードが削除されました'
GO


DELETE dbo.MyEquipment
WHERE Id = 2


DELETE dbo.MyEquipment


ALTER TRIGGER dbo.trMyEquipment_D
ON dbo.MyEquipment
AFTER DELETE -- DELETE後の処理
AS
    SELECT CAST(@@ROWCOUNT AS VARCHAR)
        + N'件のレコードが削除されました'
    SELECT * FROM deleted
GO


CREATE TRIGGER trEquipment_IU
ON dbo.Equipment
AFTER INSERT, UPDATE -- INSERT、UPDATE後の処理
AS
     -- SOUNDEX値の処理を効率化するため、
     -- ModelSDX列とMakeSDX列に値を事前に格納しておく
     UPDATE dbo.Equipment
     SET ModelSDX = SOUNDEX(Model),
         MakeSDX = SOUNDEX(Make)
     WHERE EqId IN (SELECT EqId FROM inserted)


CREATE TRIGGER trEquipment_D
ON dbo.Equipment
AFTER DELETE -- DELETE後の処理
AS
    PRINT N'Equipmentテーブルから1つまたは複数の行が削除されました'


DELETE dbo.Equipment


DELETE dbo.Equipment
WHERE EqId = 77777


ALTER TRIGGER trEquipment_IU
ON dbo.Equipment
AFTER INSERT, UPDATE -- INSERT、UPDATE後の処理
AS
     -- SOUNDEX値の処理を効率化するため、
     -- ModelSDX列とMakeSDX列に値を事前に格納しておく
    IF UPDATE(Model)
        UPDATE dbo.Equipment
        SET ModelSDX = SOUNDEX(Model)
        WHERE EqId IN (SELECT EqId FROM inserted)
    IF UPDATE(Make)
        UPDATE dbo.Equipment
        SET MakeSDX = SOUNDEX(Make)
        WHERE EqId IN (SELECT EqId FROM inserted)


UPDATE dbo.Equipment
SET Make = Make


IF COLUMNS_UPDATED() & 4 = 4
    PRINT '列3が更新されました'


CREATE TRIGGER trEquipment_U
ON dbo.Equipment
AFTER UPDATE
AS
    -- 列の更新状態を格納するテーブル変数
    DECLARE @tblUpdatedColumn TABLE
        (
            COLUMN_NAME VARCHAR(128),
            COLUMN_ID SMALLINT,
            UPDATED SMALLINT
        )

    INSERT INTO @tblUpdatedColumn
    SELECT
        COLUMN_NAME,
        COLUMNPROPERTY
        (
            OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
            COLUMN_NAME,
            'ColumnID'
        ),
        -- COLUMNS_UPDATED関数の値と当該列を指し示す整数ビットマスク
        -- を比較し、更新があったかどうかを検査する
        CASE
            WHEN SUBSTRING
                 (
                     COLUMNS_UPDATED(),
                     1 + ROUND
                         (
                             (
                                 COLUMNPROPERTY
                                 (
                                     OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
                                     COLUMN_NAME,
                                     'ColumnID'
                                 ) - 1
                             ) / 8,
                             0
                         ),
                     1
                 )
                 &
                 -- 整数ビットマスクを生成
                 POWER
                 (
                     2,
                     (
                         COLUMNPROPERTY
                         (
                             OBJECT_ID
                             (
                                  TABLE_SCHEMA + '.' + TABLE_NAME
                             ),
                             COLUMN_NAME,
                             'ColumnID'
                         ) - 1
                     ) % 8
                 ) <> 0
            THEN 1 -- 更新があった
            ELSE 0 -- 更新がなかった
       END
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Equipment';

    SELECT * FROM @tblUpdatedColumn


CREATE TRIGGER dbo.trOrderStatus_U
ON dbo.OrderHeader
AFTER UPDATE -- UPDATE後の処理
AS
    DECLARE @intOldOrderStatusId INT,
            @intNewOrderStatusId INT

    IF UPDATE(OrderStatusId)
    BEGIN
        SELECT @intOldOrderStatusId = OrderStatusId FROM deleted
        SELECT @intNewOrderStatusId = OrderStatusId FROM inserted
        INSERT INTO dbo.ActivityLog(
            Activity,
            LogDate,
            UserName,
            Note
        )
        VALUES(
            'OrderHeader.OrderStatusId',
            GETDATE(),
            USER_NAME(),
            'Value changed from '
            + CAST(@intOldOrderStatusId AS VARCHAR)
            + ' to '
            + CAST(@intNewOrderStatusId AS VARCHAR)
        )
    END


ALTER TRIGGER dbo.trOrderStatus_U
ON dbo.OrderHeader
AFTER UPDATE -- UPDATE後の処理
AS
    IF UPDATE(OrderStatusId)
    BEGIN
        INSERT INTO ActivityLog(
            Activity,
            LogDate,
            UserName,
            Note
        )
        SELECT
            'OrderHeader.OrderStatusId',
            GETDATE(),
            USER_NAME(),
            'Value changed from '
            + CAST(d.OrderStatusId AS VARCHAR)
            + ' to '
            + CAST(i.OrderStatusId AS VARCHAR)
        FROM deleted d INNER JOIN inserted i
            ON d.OrderId = i.OrderId
    END


CREATE TRIGGER dbo.trOrderHeader_D
ON dbo.OrderHeader
AFTER DELETE
AS
    BEGIN
    SET NOCOUNT ON
    INSERT INTO OrderDeleted(
        OrderId,
        OrderDate,
        RequestedById,
        TargetDate,
        CompletionDate,
        DestinationLocationId,
        Note,
        OrderTypeId,
        OrderStatusid,
        UserName,
        ChangeDT
    )
    SELECT
        OrderId,
        OrderDate,
        RequestedById,
        TargetDate,
        CompletionDate,
        DestinationLocationId,
        Note,
        OrderTypeId,
        OrderStatusid,
        SUSER_SNAME(),
        GETDATE()
    FROM deleted
    END


CREATE TRIGGER dbo.itrMyEquipment_D
ON dbo.MyEquipment
INSTEAD OF DELETE
AS
     -- このテーブルへのDELETEは許可しない
     RAISERROR(N'MyEquipmentテーブルのレコードは削除できません', 16, 1)


CREATE TRIGGER itrEqType_D
ON dbo.EqType
INSTEAD OF DELETE
AS
    IF EXISTS(SELECT * FROM Equipment
              WHERE EqTypeId IN(SELECT EqTypeId FROM deleted)
    )
        RAISERROR(
            N'EqTypeテーブルのレコードがEquipmentテーブルで参照されています',
            16,
            1
        )
    ELSE
        DELETE EqType
        WHERE EqTypeId IN(SELECT EqTypeId FROM deleted)


CREATE VIEW dbo.vEquipment
AS
SELECT Equipment.EqId,
       Equipment.Make,
       Equipment.Model,
       EqType.EqType
FROM dbo.Equipment Equipment INNER JOIN dbo.EqType EqType
    ON Equipment.EqTypeId = EqType.EqTypeId

GO

CREATE TRIGGER itr_vEquipment_I
ON dbo.vEquipment
INSTEAD OF INSERT
AS
    -- 新しいEqType値があるか調べる
    IF EXISTS(SELECT EqType
              FROM inserted
              WHERE EqType NOT IN(SELECT EqType FROM EqType)
    )
    -- 新しいEqType値を挿入する
    INSERT INTO EqType(EqType)
        SELECT EqType
        FROM inserted
        WHERE EqType NOT IN(SELECT EqType FROM EqType)

    -- 新しい備品を挿入する
    INSERT INTO Equipment(Make, Model, EqTypeId)
        SELECT inserted.Make, inserted.Model, EqType.EqTypeId
        FROM inserted INNER JOIN EqType
            ON inserted.EqType = EqType.EqType

GO

INSERT INTO vEquipment(Make, Model, EqType)
VALUES('Microsoft', 'Natural Keyboard 108', 'USB Keyboard')


EXEC sp_settriggerorder @triggername = 'trInventory_I',
                        @order = 'first',
                        @stmttype = 'INSERT'

---------------------------------------------------------------------
-- 9.2 DDLトリガ
---------------------------------------------------------------------

CREATE TRIGGER trdPreventTableChanges
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
    RAISERROR(N'このデータベースには重要なデータが格納されています。テーブルの変更は許可されず、トリガによってロールバックされます。テーブルの変更が許可されている場合は、次のステートメントを実行して一時的にトリガを無効にしてください：

DISABLE TRIGGER trdPreventTableChanges ON DATABASE
<ここに、テーブルを変更するバッチを記述>
ENABLE TRIGGER trdPreventTableChanges ON DATABASE;

', 16, 1)
    ROLLBACK


CREATE TRIGGER trdPreventLoginCreation
ON ALL SERVER FOR CREATE_LOGIN
AS
    RAISERROR(N'このサーバーにログインを追加するには権限が必要です。DBAセキュリティグループにお問い合わせください', 16, 1)

    ROLLBACK

    INSERT INTO Asset5.dbo.ActivityLog(Activity, LogDate, UserName)
    VALUES('LOGIN CHANGE', GETDATE(), SYSTEM_USER)


CREATE TRIGGER trdPreventLoginChanges
ON ALL SERVER FOR DDL_LOGIN_EVENTS
AS
    RAISERROR(N'このサーバーでログインを追加／変更するには権限が必要です。DBAセキュリティグループにお問い合わせください', 16, 1)

    ROLLBACK


CREATE TRIGGER trdAuditTableChanges
ON DATABASE FOR DDL_TABLE_EVENTS
AS
    DECLARE @event VARCHAR(max)
    SET @event = CONVERT(NVARCHAR(max), EVENTDATA())

    INSERT INTO Asset5.dbo.ActivityLog(Activity, LogDate, UserName, Note)
    VALUES('TABLE CHANGE', GETDATE(), SYSTEM_USER, @event)


CREATE TRIGGER trdAuditTableChanges
ON DATABASE
AFTER DDL_TABLE_EVENTS
AS
    DECLARE @event XML
    SET @event = EVENTDATA()

    INSERT INTO Asset5.dbo.ActivityLog(
        Activity,
        LogDate,
        UserName,
        Note
    )
    VALUES(
        @event.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        GETDATE(),
        @event.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)'),
        @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(2000)')
    )

---------------------------------------------------------------------
-- 9.3 トリガの管理
---------------------------------------------------------------------

EXEC sp_helptrigger 'dbo.OrderHeader'


EXEC sp_helptext 'dbo.trOrderStatus_U'


IF EXISTS (SELECT * FROM sys.objects
           WHERE object_id = OBJECT_ID(N'[dbo].[trEquipment_IU]')
)
DROP TRIGGER trEquipment_IU


IF EXISTS(SELECT * FROM master.sys.server_triggers
           WHERE name = N'trdPreventLoginCreation'
)
DROP TRIGGER trdPreventLoginCreation ON ALL SERVER
GO

IF EXISTS(SELECT * FROM sys.triggers
           WHERE name = N'trdAuditTableChanges'
           AND parent_class = 0
)
DROP TRIGGER trdAuditTableChanges ON DATABASE


ALTER TABLE Equipment DISABLE TRIGGER trEquipment_IU


DISABLE TRIGGER trEquipment_IU ON Equipment


DISABLE TRIGGER trdPreventLoginCreation ON ALL SERVER

---------------------------------------------------------------------
-- 9.4 トリガを設計するうえでのポイント
---------------------------------------------------------------------

ALTER TRIGGER trOrderStatus_U
ON dbo.OrderHeader
AFTER UPDATE -- UPDATE後の処理
AS
    PRINT N'trOrderStatus_Uを開始'
    IF @@ROWCOUNT = 0 -- この条件式は常に真であり、残りの
                      -- コードが実行されることは決してない
        RETURN

    IF UPDATE(OrderStatusId)
    BEGIN
        INSERT INTO ActivityLog(
            Activity,
            LogDate,
            UserName,
            Note
        )
        SELECT
            'OrderHeader.OrderStatusId',
            GETDATE(),
            USER_NAME(),
            'Value changed from '
            + CAST(d.OrderStatusId AS VARCHAR)
            + ' to '
            + CAST(i.OrderStatusId AS VARCHAR)
        FROM deleted d INNER JOIN inserted i
            ON d.OrderId = i.OrderId
    END

---------------------------------------------------------------------
-- 9.5 トリガ内でのトランザクション管理
---------------------------------------------------------------------

CREATE TRIGGER trdPreventLoginCreation
ON ALL SERVER FOR CREATE_LOGIN
AS
    RAISERROR(N'このサーバーにログインを追加するには権限が必要です。DBAセキュリティグループにお問い合わせください', 16, 1)

    ROLLBACK

    INSERT INTO Asset5.dbo.ActivityLog(Activity, LogDate, UserName)
    VALUES('LOGIN CHANGE', GETDATE(), SYSTEM_USER)

---------------------------------------------------------------------
-- 9.6 トリガの用途
---------------------------------------------------------------------

DELETE dbo.InventoryProperty
WHERE InventoryId = 222

DELETE dbo.Inventory
WHERE InventoryId = 222


DELETE dbo.Inventory
WHERE InventoryId = 222


CREATE TABLE dbo.MyInventory(
    Inventoryid INT NOT NULL IDENTITY(1, 1),
    EqId INT NULL,
    LocationId INT NULL,
    StatusId TINYINT NULL,
    LeaseId INT NULL,
    LeaseScheduleId INT NULL,
    OwnerId INT NULL,
    Rent SMALLMONEY NULL,
    Lease SMALLMONEY NULL,
    Cost SMALLMONEY NULL,
    AcquisitionTypeID TINYINT NULL
)

GO

ALTER TABLE dbo.MyInventory ADD CONSTRAINT
    PK_Inventory PRIMARY KEY NONCLUSTERED(
        Inventoryid
    )

GO

CREATE TABLE dbo.MyInventoryProperty(
    InventoryId INT NOT NULL,
    PropertyId SMALLINT NOT NULL,
    Value VARCHAR(50) NULL
)

GO

ALTER TABLE dbo.MyInventoryProperty ADD CONSTRAINT
    PK_InventoryProperty PRIMARY KEY NONCLUSTERED(
        InventoryId,
        PropertyId
    )

GO

CREATE TRIGGER dbo.trMyInventory_CascadingDelete_D
ON dbo.MyInventory
AFTER DELETE -- DELETE後の処理
AS
    IF @@ROWCOUNT = 0
        RETURN
    DELETE dbo.MyInventoryProperty
    WHERE InventoryId IN(SELECT InventoryID FROM deleted)

GO

INSERT INTO dbo.MyInventory(EqId) VALUES(1)
INSERT INTO dbo.MyInventory(EqId) VALUES(2)
INSERT INTO dbo.MyInventory(EqId) VALUES(3)
INSERT INTO dbo.MyInventory(EqId) VALUES(4)
INSERT INTO dbo.MyInventory(EqId) VALUES(5)

INSERT INTO dbo.MyInventoryProperty(InventoryId, PropertyId, Value)
VALUES(1, 1, 'ACME')
INSERT INTO dbo.MyInventoryProperty(InventoryId, PropertyId, Value)
VALUES(1, 2, 'Turbo')
INSERT INTO dbo.MyInventoryProperty(InventoryId, PropertyId, Value)
VALUES(1, 3, '311')
INSERT INTO dbo.MyInventoryProperty(InventoryId, PropertyId, Value)
VALUES(2, 1, 'ACME')
INSERT INTO dbo.MyInventoryProperty(InventoryId, PropertyId, Value)
VALUES(2, 2, 'TurboPro')
INSERT INTO dbo.MyInventoryProperty(InventoryId, PropertyId, Value)
VALUES(2, 3, '312')

GO

DELETE dbo.MyInventory
WHERE InventoryId = 1

SELECT * FROM dbo.MyInventory
SELECT * FROM dbo.MyInventoryProperty


CREATE TRIGGER dbo.trInventory_Lease_I
ON dbo.Inventory
AFTER INSERT -- INSERT後の処理
AS
    IF @@ROWCOUNT = 0
        RETURN

    -- リース料の合計を格納するためのテーブル変数
    DECLARE @tblSumOfLease TABLE(
        LeaseScheduleId INT,
        SumOfLease MONEY
    )

    -- 挿入された各備品のリース料をリーススケジュールごとに合算する
    INSERT INTO @tblSumOfLease
    SELECT LeaseScheduleId, SUM(COALESCE(LEASE, 0))
    FROM inserted
    WHERE LeaseScheduleId IN(SELECT LeaseScheduleId FROM inserted
                             WHERE LeaseScheduleId IS NOT NULL
                             GROUP BY LeaseScheduleId)
    GROUP BY LeaseScheduleId

    -- 挿入した備品のリース料を総額に加算する
    UPDATE LeaseSchedule
    SET LeaseSchedule.PeriodicTotalAmount =
        LeaseSchedule.PeriodicTotalAmount + tblSumOfLease.SumOfLease
    FROM dbo.LeaseSchedule LeaseSchedule INNER JOIN @tblSumOfLease tblSumOfLease
        ON LeaseSchedule.ScheduleId = tblSumOfLease.LeaseScheduleId


CREATE TRIGGER dbo.trInventory_Lease_D
ON dbo.Inventory
AFTER DELETE -- DELETE後の処理
AS
    IF @@ROWCOUNT = 0
        RETURN

-- リース料の合計を格納するためのテーブル変数
DECLARE @tblSumOfLease TABLE(
    LeaseScheduleId INT,
    SumOfLease MONEY
)

-- 削除された各備品のリース料をリーススケジュールごとに合算する
INSERT INTO @tblSumOfLease
SELECT LeaseScheduleId, SUM(COALESCE(LEASE, 0))
FROM deleted
WHERE LeaseScheduleId IN(SELECT LeaseScheduleId FROM deleted
                         WHERE LeaseScheduleId IS NOT NULL
                         GROUP BY LeaseScheduleId)
GROUP BY LeaseScheduleId

-- 挿入した備品のリース料を総額に加算する
UPDATE LeaseSchedule
SET LeaseSchedule.PeriodicTotalAmount =
    LeaseSchedule.PeriodicTotalAmount - tblSumOfLease.SumOfLease
FROM dbo.LeaseSchedule LeaseSchedule INNER JOIN @tblSumOfLease tblSumOfLease
    ON LeaseSchedule.ScheduleId = tblSumOfLease.LeaseScheduleId


ALTER TRIGGER dbo.trInventory_Lease_U
ON dbo.Inventory
AFTER UPDATE -- UPDATE後の処理
AS
    IF @@ROWCOUNT = 0
        RETURN

    IF UPDATE(Lease) OR UPDATE(LeaseScheduleId)
        BEGIN
        -- リース料の合計を格納するためのテーブル変数
        DECLARE @tblSumOfLease_D TABLE(
            LeaseScheduleId INT,
            SumOfLease MONEY
        )

        -- 削除された各備品のリース料をリーススケジュールごとに合算する
        INSERT INTO @tblSumOfLease_D
        SELECT LeaseScheduleId, SUM(COALESCE(LEASE, 0))
        FROM deleted
        WHERE LeaseScheduleId IN(SELECT LeaseScheduleId FROM deleted
                                 WHERE LeaseScheduleId IS NOT NULL
                                 GROUP BY LeaseScheduleId)
        GROUP BY LeaseScheduleId

        -- 挿入した備品のリース料を総額に加算する
        UPDATE LeaseSchedule
        SET LeaseSchedule.PeriodicTotalAmount =
            LeaseSchedule.PeriodicTotalAmount - tblSumOfLease_D.SumOfLease
        FROM dbo.LeaseSchedule LeaseSchedule
        INNER JOIN @tblSumOfLease_D tblSumOfLease_D
            ON LeaseSchedule.ScheduleId = tblSumOfLease_D.LeaseScheduleId

        DECLARE @tblSumOfLease_I TABLE(
            LeaseScheduleId INT,
            SumOfLease MONEY
        )

        -- 挿入された各備品のリース料をリーススケジュールごとに合算する
        INSERT INTO @tblSumOfLease_I
        SELECT LeaseScheduleId, SUM(COALESCE(LEASE, 0))
        FROM inserted
        WHERE LeaseScheduleId IN(SELECT LeaseScheduleId FROM inserted
                                 WHERE LeaseScheduleId IS NOT NULL
                                 GROUP BY LeaseScheduleId)
        GROUP BY LeaseScheduleId

        -- 挿入した備品のリース料を総額に加算する
        UPDATE LeaseSchedule
        SET LeaseSchedule.PeriodicTotalAmount =
            LeaseSchedule.PeriodicTotalAmount + tblSumOfLease_I.SumOfLease
        FROM dbo.LeaseSchedule LeaseSchedule
        INNER JOIN @tblSumOfLease_I tblSumOfLease_I
            ON LeaseSchedule.ScheduleId = tblSumOfLease_I.LeaseScheduleId
    END


CREATE DATABASE Quarterly
GO
USE Quarterly
GO
CREATE TABLE Sales1(
    PartId INT,
    CustomerId INT,
    SalesDate SMALLINT,
    Sales FLOAT
)
GO

CREATE TABLE Sales2(
    PartId INT,
    CustomerId INT,
    SalesDate SMALLINT,
    Sales FLOAT
)
GO

CREATE TABLE Sales3(
    PartId INT,
    CustomerId INT,
    SalesDate SMALLINT,
    Sales FLOAT
)
GO

CREATE TABLE Sales4(
    PartId INT,
    CustomerId INT,
    SalesDate SMALLINT,
    Sales FLOAT
)
GO


CREATE TRIGGER trdReplicateSalesTableChanges
ON DATABASE
AFTER DDL_TABLE_EVENTS
AS
    PRINT 'trdReplicateSalesTableChanges started.'
    DECLARE @xmlEvent XML
    DECLARE @sysObject SYSNAME,
            @sysObjectType SYSNAME,
            @chvnTSQLCommand NVARCHAR(max),
            @chvnTSQLCommand2 NVARCHAR(max),
            @i INT

    SET @xmlEvent = EVENTDATA()
    SET @sysObject = @xmlEvent.value(
        '(/EVENT_INSTANCE/ObjectName)[1]',
        'NVARCHAR(100)'
    )
    SET @sysObjectType = @xmlEvent.value(
        '(/EVENT_INSTANCE/ObjectType)[1]',
        'NVARCHAR(100)'
    )
    SET @chvnTSQLCommand = @xmlEvent.value(
        '(/EVENT_INSTANCE/TSQLCommand)[1]',
        'NVARCHAR(max)'
    )

    IF (@sysObjectType = 'TABLE')
    BEGIN
        IF (@sysObject = 'Sales1')
        BEGIN
            -- Sales1の文字列をSales2〜Sales4に置換する
            SET @i = 2
            WHILE @i <= 4
            BEGIN
                SET @chvnTSQLCommand2 = REPLACE(
                                            @chvnTSQLCommand,
                                            'Sales1',
                                            'Sales' + CAST(@i AS VARCHAR)
                                        )
                -- 実行
                PRINT @chvnTSQLCommand2
                EXEC sp_executeSql @chvnTSQLCommand2
                SET @i = @i + 1
            END
        END
    END


ALTER TABLE Sales1
   ADD StoreId INT NULL


trdReplicateSalesTableChanges started.
ALTER TABLE Sales2
   ADD StoreId INT NULL
ALTER TABLE Sales3
   ADD StoreId INT NULL
ALTER TABLE Sales4
   ADD StoreId INT NULL

