Trigger

adalah batch/sekumpulan perintah yang secara otomatis dikerjakan ketika terjadi penyisipan (insert), pengubahan (update) atau penghapusan data (delete) pada sebuah tabel. 

TRIGGER UPDATE

CREATE TRIGGER dbo.TriggerUpdateNilai

   ON  TBNilai

   AFTER UPDATE

AS

BEGIN

      DECLARE @NRP nchar(10)

      DECLARE @KodeMK char(5)

      DECLARE @Nilai char(2)

       

      -- Deletion Commandment

      SELECT @NRP = NRP, @KodeMK = KodeMK, @Nilai = Nilai FROM DELETED

     

      INSERT INTO DBTrigger.dbo.TBLog

      VALUES (getdate(), 'DELETE', Suser_Sname(), @NRP + '/' + @KodeMK + '/' + @Nilai)

    -- Insertion Commandment

      SELECT @NRP = NRP, @KodeMK = KodeMK, @Nilai = Nilai FROM INSERTED

     

      INSERT INTO DBTrigger.dbo.TBLog

      VALUES (getdate(), 'INSERT', Suser_Sname(), @NRP + '/' + @KodeMK + '/' + @Nilai)

     

      END

GO

Select * from TBNilai

Select * from TBLog

--

INSERT BARANG GANTI STOK

CREATE TRIGGER dbo.SinkroInsertion

   ON  TBJual

   AFTER INSERT

AS

BEGIN

      DECLARE @Qty int

      DECLARE @Kode varchar(10)

      -- Insertion Commandment

      SELECT @Qty = Qty, @Kode = Kode FROM INSERTED

     

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty

      WHERE Kode = @Kode

END

GO

--

DELETE BARANG

ALTER TRIGGER dbo.SinkroDeletion

   ON  TBJual

   AFTER DELETE

AS

BEGIN

      DECLARE @Qty int

      DECLARE @Kode varchar(10)

      --  delete Commandment

      SELECT @Qty = Qty, @Kode = Kode FROM DELETED

     

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qty

      WHERE Kode = @Kode

END

GO

UPDATE

select * from

ALTER TRIGGER dbo.SinkroUpdate

   ON  TBJual

   AFTER UPDATE

AS

BEGIN

      DECLARE @Qtylama int

      DECLARE @Qtybaru int

      DECLARE @Kode varchar(10)

      -- UPDATE Commandment

      SELECT @Qtylama = Qty, @Kode = Kode FROM DELETED

      SELECT @Qtybaru = Qty FROM INSERTED

     

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

END

GO

MODIFIKASI 

USE [DBTrigger]

GO

/****** Object:  Trigger [dbo].[SinkroInsertion]    Script Date: 01/13/2011 10:42:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[SinkroInsertion]

   ON  [dbo].[TBJual]

   AFTER INSERT

AS

BEGIN

      DECLARE @Qty int

      DECLARE @Kode varchar(10)

      DECLARE @TotalJual money

      DECLARE @Harga money

      DECLARE @TglFJ datetime

     

      -- Insertion Commandment

      SELECT @Qty = Qty, @Kode = Kode, @Harga = Harga, @TglFJ = TglFJ FROM INSERTED

     

      SET @TotalJual = @Qty * @Harga

     

      IF datepart(mm, @TglFJ) = 1

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual1 = Jual1 + @TotalJual,

       Qty1 = Qty1 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 2

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual2 = Jual2 + @TotalJual,

       Qty2 = Qty2 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 3

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual3 = Jual3 + @TotalJual,

       Qty3 = Qty3 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 4

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual4 = Jual4 + @TotalJual,

       Qty4 = Qty4 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 5

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual5 = Jual5 + @TotalJual,

       Qty5 = Qty5 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 6

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual6 = Jual6 + @TotalJual,

       Qty6 = Qty6 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 7

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual7 = Jual7 + @TotalJual,

       Qty7 = Qty7 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 8

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual8 = Jual8 + @TotalJual,

       Qty8 = Qty8 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 9

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual9 = Jual9 + @TotalJual,

       Qty9 = Qty9 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 10

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual10 = Jual10 + @TotalJual,

       Qty10 = Qty10 + @Qty

      WHERE Kode = @Kode 

     

      ELSE IF datepart(mm, @TglFJ) = 11

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual11 = Jual11 + @TotalJual,

       Qty11 = Qty11 + @Qty

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 12

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok - @Qty,

       Jual2 = Jual2 + @TotalJual,

       Qty12 = Qty12 + @Qty

      WHERE Kode = @Kode

END

EDIT

USE [DBTrigger]

GO

/****** Object:  Trigger [dbo].[SinkroUpdate]    Script Date: 01/13/2011 10:59:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[SinkroUpdate]

   ON  [dbo].[TBJual]

   AFTER UPDATE

AS

BEGIN

      DECLARE @Qtylama int

      DECLARE @Qtybaru int

      DECLARE @Kode varchar(10)

      DECLARE @TotalJuallama money

      DECLARE @TotalJualbaru money

      DECLARE @Hargalama money

      DECLARE @Hargabaru money

      DECLARE @TglFJ datetime

      -- UPDATE Commandment

      SELECT @Qtylama = Qty, @Kode = Kode, @Hargalama = Harga, @TglFJ = TglFJ  FROM DELETED

      SELECT @Qtybaru = Qty, @Hargabaru = Harga FROM INSERTED

     

      SET @TotalJuallama = @Qtylama * @Hargalama

      SET @TotalJualbaru = @Qtybaru * @Hargabaru

     

      IF datepart(mm, @TglFJ) = 1

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual1 = Jual1 + @TotalJuallama - @TotalJualBaru,

       Qty1 = Qty1 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 2

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual2 = Jual2 + @TotalJuallama - @TotalJualBaru,

       Qty2 = Qty2 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 3

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual3 = Jual1 + @TotalJuallama - @TotalJualBaru,

       Qty3 = Qty1 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 4

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual4 = Jual4 + @TotalJuallama - @TotalJualBaru,

       Qty4 = Qty4 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 5

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual5 = Jual5 + @TotalJuallama - @TotalJualBaru,

       Qty5 = Qty5 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 6

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual6 = Jual6 + @TotalJuallama - @TotalJualBaru,

       Qty6 = Qty6 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 7

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual7 = Jual7 + @TotalJuallama - @TotalJualBaru,

       Qty7 = Qty7 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 8

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual8 = Jual8 + @TotalJuallama - @TotalJualBaru,

       Qty8 = Qty8 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 9

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual9 = Jual9 + @TotalJuallama - @TotalJualBaru,

       Qty9 = Qty9 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 10

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual10 = Jual10 + @TotalJuallama - @TotalJualBaru,

       Qty10 = Qty10 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 11

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual11 = Jual11 + @TotalJuallama - @TotalJualBaru,

       Qty11 = Qty11 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

     

      ELSE IF datepart(mm, @TglFJ) = 12

      UPDATE DBTrigger.dbo.TBBarang

      SET Stok = Stok + @Qtylama - @Qtybaru,

       Jual12 = Jual12 + @TotalJuallama - @TotalJualBaru,

       Qty12 = Qty12 + @Qtylama - @Qtybaru

      WHERE Kode = @Kode

END