Contoh Trigger Basis Data

CREATE TRIGGER TrigUpdateDisc ON [Order Details]

AFTER UPDATE AS

BEGIN

Declare @Discount real,@DiscLama real,@DiscBaru

SELECT @DiscLama = Discount FROM deleted

SELECT @DiscBaru = Discount FROM inserted

UPDATE Order Details

         END

         GO

2.    CREATE TRIGGER DeleteEmplo ON Employee

AFTER DELETE AS

BEGIN

           

                        DECLARE @x nvarchar(20)

           

                        SELECT @x = EmployeeID FROM DELETED

           

                        PRINT 'Employee ' + @x + ' Terminated Successfully'

GO

DELETE FROM Products WHERE EmployeeID = 'Schwarzenneger'

3.    CREATE TRIGGER DisabledInser ON Product

AFTER INSERT AS

BEGIN

PRINT 'You have no right to insert anything'

ROLLBACK TRANSACTION

END

GO

INSERT INTO Product (ProductID, ProductName) VALUES ('01415', 'Truck')

4.      Trigger Untuk Menentukan Umur Pegawai

ALTER TABLE Employees

ADD Age Int NOT NULL DEFAULT 0

Query diatas untuk menciptakan attribute/kolom age dari pegawai.

          CREATE TRIGGER TrigUmurPeg ON Employees

          AFTER INSERT AS

          BEGIN

declare @ FName nvarchar(10), LName nvarchar (20), @TglLahir datetime

SELECT @Fname = FirstName, @LName = LastName, @TglLahir = BirthDate FROM inserted

INSERT INTO Employees (Age)

VALUES (Datediff(year,BirthDate,GetDate())

END

GO

   CREATE TRIGGER TrigUpdateAlamatCust ON Customers

AFTER UPDATE AS

BEGIN

declare @CustomerID nchar(5), OldAddr nvarchar (60), NewAddr nvarchar(60)

               SELECT @OldAddr = Address FROM deleted

SELECT @CustomerID = CustomerID, @NewAddr = Address FROM inserted

Print ‘Data Pelanggan ‘+@CustomerID+ ‘ Telah Diupdate’

Print ‘Alamat Baru Pelanggan : ‘ + @NewAddr

          BEGIN

          GO