---------------------------------------------------------------------
-- SQL Server 2005XgAhvV[WvO~O
-- 9 gK
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 9.1 DMLgK
---------------------------------------------------------------------

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

-- e[uɃR[hi[
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'̃R[h폜܂'
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'̃R[h폜܂'
    SELECT * FROM deleted
GO


CREATE TRIGGER trEquipment_IU
ON dbo.Equipment
AFTER INSERT, UPDATE -- INSERTAUPDATȄ
AS
     -- SOUNDEXl̏邽߁A
     -- ModelSDXMakeSDXɒlOɊi[Ă
     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'Equipmente[u1܂͕̍s폜܂'


DELETE dbo.Equipment


DELETE dbo.Equipment
WHERE EqId = 77777


ALTER TRIGGER trEquipment_IU
ON dbo.Equipment
AFTER INSERT, UPDATE -- INSERTAUPDATȄ
AS
     -- SOUNDEXl̏邽߁A
     -- ModelSDXMakeSDXɒlOɊi[Ă
    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 '3XV܂'


CREATE TRIGGER trEquipment_U
ON dbo.Equipment
AFTER UPDATE
AS
    -- ̍XVԂi[e[uϐ
    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֐̒lƓYwrbg}XN
        -- rAXVǂ
        CASE
            WHEN SUBSTRING
                 (
                     COLUMNS_UPDATED(),
                     1 + ROUND
                         (
                             (
                                 COLUMNPROPERTY
                                 (
                                     OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
                                     COLUMN_NAME,
                                     'ColumnID'
                                 ) - 1
                             ) / 8,
                             0
                         ),
                     1
                 )
                 &
                 -- rbg}XN𐶐
                 POWER
                 (
                     2,
                     (
                         COLUMNPROPERTY
                         (
                             OBJECT_ID
                             (
                                  TABLE_SCHEMA + '.' + TABLE_NAME
                             ),
                             COLUMN_NAME,
                             'ColumnID'
                         ) - 1
                     ) % 8
                 ) <> 0
            THEN 1 -- XV
            ELSE 0 -- XVȂ
       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
     -- ̃e[uւDELETE͋Ȃ
     RAISERROR(N'MyEquipmente[ũR[h͍폜ł܂', 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'EqTypee[ũR[hEquipmente[uŎQƂĂ܂',
            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
    -- VEqTypel邩ׂ
    IF EXISTS(SELECT EqType
              FROM inserted
              WHERE EqType NOT IN(SELECT EqType FROM EqType)
    )
    -- VEqTypel}
    INSERT INTO EqType(EqType)
        SELECT EqType
        FROM inserted
        WHERE EqType NOT IN(SELECT EqType FROM EqType)

    -- Vi}
    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 DDLgK
---------------------------------------------------------------------

CREATE TRIGGER trdPreventTableChanges
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
    RAISERROR(N'̃f[^x[Xɂ͏dvȃf[^i[Ă܂Be[u̕ύX͋ꂸAgKɂă[obN܂Be[u̕ύXĂꍇ́ÃXe[ggsĈꎞIɃgK𖳌ɂĂF

DISABLE TRIGGER trdPreventTableChanges ON DATABASE
<ɁAe[uύXob`Lq>
ENABLE TRIGGER trdPreventTableChanges ON DATABASE;

', 16, 1)
    ROLLBACK


CREATE TRIGGER trdPreventLoginCreation
ON ALL SERVER FOR CREATE_LOGIN
AS
    RAISERROR(N'̃T[o[ɃOCǉɂ͌KvłBDBAZLeBO[vɂ₢킹', 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'̃T[o[ŃOCǉ^ύXɂ͌KvłBDBAZLeBO[vɂ₢킹', 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 gK̊Ǘ
---------------------------------------------------------------------

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 gK݌v邤ł̃|Cg
---------------------------------------------------------------------

ALTER TRIGGER trOrderStatus_U
ON dbo.OrderHeader
AFTER UPDATE -- UPDATȄ
AS
    PRINT N'trOrderStatus_UJn'
    IF @@ROWCOUNT = 0 -- ̏͏ɐ^łAc
                      -- R[hs邱Ƃ͌ĂȂ
        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 gKł̃gUNVǗ
---------------------------------------------------------------------

CREATE TRIGGER trdPreventLoginCreation
ON ALL SERVER FOR CREATE_LOGIN
AS
    RAISERROR(N'̃T[o[ɃOCǉɂ͌KvłBDBAZLeBO[vɂ₢킹', 16, 1)

    ROLLBACK

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

---------------------------------------------------------------------
-- 9.6 gK̗pr
---------------------------------------------------------------------

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

    -- [X̍vi[邽߂̃e[uϐ
    DECLARE @tblSumOfLease TABLE(
        LeaseScheduleId INT,
        SumOfLease MONEY
    )

    -- }ꂽeĩ[X[XXPW[ƂɍZ
    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

    -- }ĩ[X𑍊zɉZ
    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

-- [X̍vi[邽߂̃e[uϐ
DECLARE @tblSumOfLease TABLE(
    LeaseScheduleId INT,
    SumOfLease MONEY
)

-- 폜ꂽeĩ[X[XXPW[ƂɍZ
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

-- }ĩ[X𑍊zɉZ
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
        -- [X̍vi[邽߂̃e[uϐ
        DECLARE @tblSumOfLease_D TABLE(
            LeaseScheduleId INT,
            SumOfLease MONEY
        )

        -- 폜ꂽeĩ[X[XXPW[ƂɍZ
        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

        -- }ĩ[X𑍊zɉZ
        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
        )

        -- }ꂽeĩ[X[XXPW[ƂɍZ
        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

        -- }ĩ[X𑍊zɉZ
        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ɒu
            SET @i = 2
            WHILE @i <= 4
            BEGIN
                SET @chvnTSQLCommand2 = REPLACE(
                                            @chvnTSQLCommand,
                                            'Sales1',
                                            'Sales' + CAST(@i AS VARCHAR)
                                        )
                -- s
                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

