ตอนที่ 15 : การสร้าง Trigger บน SQL Server Database (SQL Server : Trigger)

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
    -- DELETED
    -- INSERTED

END

CREATE TRIGGER DelCustomer
   ON  [CUSTOMER]
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;
	
	-- Declare Variable
	DECLARE @sCustomerID VARCHAR(4);
	
	-- Get CUSTOMER_ID from delete rows
	SET @sCustomerID = (SELECT CUSTOMER_ID FROM DELETED)
	
	-- Delete related table
	DELETE FROM [AUDIT]
		WHERE CUSTOMER_ID = @sCustomerID;

END

CREATE TRIGGER [dbo].[SumUsed]
   ON  [dbo].[AUDIT]
   AFTER INSERT,UPDATE,DELETE
AS 
BEGIN
	SET NOCOUNT ON;
	
	-- Declare Variable
	DECLARE @sCustomerID VARCHAR(4);
	DECLARE @dUsed DECIMAL(18,2);
	
	-- Get CUSTOMER_ID from Insert or Update rows
	SET @sCustomerID = (SELECT CUSTOMER_ID FROM INSERTED)

	-- Get CUSTOMER_ID from delete (When @sCustomerID IS NULL)
	IF @sCustomerID IS NULL
	BEGIN
		SET @sCustomerID = (SELECT CUSTOMER_ID FROM DELETED)
	END

	-- Get SUM from insert/update AUDIT
	SET @dUsed = (SELECT SUM(USED) FROM [AUDIT] WHERE CUSTOMER_ID = @sCustomerID)
	
	-- Update USED in CUSTOMER
	UPDATE CUSTOMER SET USED = @dUsed
		WHERE CUSTOMER_ID = @sCustomerID;

END