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

No comments:
Write komentar

Silahkan tinggalkan komentar dansaran anda kami akan segera membalasas komentar anda dengan senang :) :)