Knowledge base

MS SQL-server trigger

The examples given below are based on a minimum SQL-server version of 7. Use Query analyzer to create the tables and triggers.

Everything put between <> should be converted to the name relevant in your application.

The trigger examples below use only 2 alphanumeric and 1 numeric primary key field. Adapt the script to your specific needs.

Creating HEAD and TRB

CREATE TABLE HEAD (
            HEADER NUMERIC NOT NULL,
            MAXI NUMERIC NOT NULL,
            PRIMARY KEY (HEADER))
go 
INSERT INTO HEAD VALUES(1,100)
go


CREATE TABLE TRB (
            IND NUMERIC NOT NULL,
            TABL VARCHAR(50) NOT NULL,
            EVENT VARCHAR(1) NOT NULL,
            SK1 VARCHAR(50) NULL,
            SK2 VARCHAR(50) NULL,
            SK3 VARCHAR(50) NULL,
            SK4 VARCHAR(50) NULL,
            SK5 VARCHAR(50) NULL,
            NK1 NUMERIC NULL,
            NK2 NUMERIC NULL,
            NK3 NUMERIC NULL,
            NK4 NUMERIC NULL,
            NK5 NUMERIC NULL,
            FLD VARCHAR(50) NULL,
            PRIMARY KEY (IND))
go 
INSERT INTO TRB (IND,TABL,EVENT,SK1) VALUES (1,'Promes','1','Promes')
go

Remark: The value 100 in the MAXI field of HEAD should be the real maximum of your circular buffer. Most likely this is even more then 10000. The insert in TRB is needed because the trigger would fail to run the first time.

Creating an insert trigger

CREATE TRIGGER <Trigger name> on <Table name> for INSERT AS
DECLARE
    @realhead NUMERIC,
    @in_table NUMERIC,
    @maximum NUMERIC,
    @sk1 VARCHAR(50),
    @sk2 VARCHAR(50),
    @sk3 VARCHAR(50),
    @sk4 VARCHAR(50),
    @sk5 VARCHAR(50),
    @nk1 NUMERIC,
    @nk2 NUMERIC,
    @nk3 NUMERIC,
    @nk4 NUMERIC,
    @nk5 NUMERIC
BEGIN
    set CURSOR_CLOSE_ON_COMMIT on
    declare <Cursor name> cursor for select <Alpha field1>,<Alpha field2>,<Numeric field1> from inserted
    open <Cursor name>
    fetch next from <Cursor name> into @sk1,@sk2,@nk1
    WHILE (@@FETCH_STATUS <> -1)
    begin
        SELECT @realhead = HEADER,@maximum = MAXI FROM HEAD
        SELECT @in_table = MAX(IND) FROM TRB
        set @realhead=@realhead+1
        IF @realhead>@maximum 
        BEGIN
            set @realhead=1
        END
        IF @in_table<@realhead 
        BEGIN
            INSERT INTO TRB (IND,TABL,EVENT,SK1,SK2,SK3,SK4,SK5,NK1,NK2,NK3,NK4,NK5) values
            (@realhead,'<Table name>','1',@sk1,@sk2,NULL,NULL,NULL,@nk1,NULL,NULL,NULL,NULL)
            UPDATE HEAD SET HEADER=@realhead
        END 
        ELSE
        BEGIN
            UPDATE TRB SET IND=@realhead,TABL='<Table name>',EVENT='1',SK1=@sk1,
            SK2=@sk2,SK3=NULL,SK4=NULL,SK5=NULL,NK1=@nk1,NK2=NULL,NK3=NULL,NK4=NULL,NK5=NULL WHERE IND=@realhead;
            UPDATE HEAD SET HEADER=@realhead;
        END
        fetch next from <Cursor name> into @sk1,@sk2,@nk1
    end
    close <Cursor name>
    deallocate <Cursor name>
    commit 
END

GO

Creating an update trigger

CREATE TRIGGER <Trigger name> on <Table name> for UPDATE AS
DECLARE
    @realhead NUMERIC,
    @in_table NUMERIC,
    @maximum NUMERIC,
    @sk1 VARCHAR(50),
    @sk2 VARCHAR(50),
    @sk3 VARCHAR(50),
    @sk4 VARCHAR(50),
    @sk5 VARCHAR(50),
    @nk1 NUMERIC,
    @nk2 NUMERIC,
    @nk3 NUMERIC,
    @nk4 NUMERIC,
    @nk5 NUMERIC
BEGIN
    set CURSOR_CLOSE_ON_COMMIT on
    declare <Cursor name> cursor for select <Alpha field1>,<Alpha field2>,<Numeric field1> from inserted
    open <Cursor name>
    fetch next from <Cursor name> into @sk1,@sk2,@nk1
    WHILE (@@FETCH_STATUS <> -1)
    begin
        SELECT @realhead = HEADER,@maximum = MAXI FROM HEAD
        SELECT @in_table = MAX(IND) FROM TRB
        set @realhead=@realhead+1
        IF @realhead>@maximum 
        BEGIN
            set @realhead=1
        END
        IF @in_table<@realhead 
        BEGIN
            INSERT INTO TRB (IND,TABL,EVENT,SK1,SK2,SK3,SK4,SK5,NK1,NK2,NK3,NK4,NK5) values
            (@realhead,'<Table name>','2',@sk1,@sk2,NULL,NULL,NULL,@nk1,NULL,NULL,NULL,NULL)
            UPDATE HEAD SET HEADER=@realhead
        END 
        ELSE
        BEGIN
            UPDATE TRB SET IND=@realhead,TABL='<Table name>',EVENT='2',SK1=@sk1,
            SK2=@sk2,SK3=NULL,SK4=NULL,SK5=NULL,NK1=@nk1,NK2=NULL,NK3=NULL,NK4=NULL,NK5=NULL WHERE IND=@realhead;
            UPDATE HEAD SET HEADER=@realhead;
        END
        fetch next from <Cursor name> into @sk1,@sk2,@nk1
    end
    close <Cursor name>
    deallocate <Cursor name>
    commit 
END

GO

Creating a delete trigger

CREATE TRIGGER <Trigger name> on <Table name> for DELETE AS
DECLARE
    @realhead NUMERIC,
    @in_table NUMERIC,
    @maximum NUMERIC,
    @sk1 VARCHAR(50),
    @sk2 VARCHAR(50),
    @sk3 VARCHAR(50),
    @sk4 VARCHAR(50),
    @sk5 VARCHAR(50),
    @nk1 NUMERIC,
    @nk2 NUMERIC,
    @nk3 NUMERIC,
    @nk4 NUMERIC,
    @nk5 NUMERIC
BEGIN
    set CURSOR_CLOSE_ON_COMMIT on
    declare <Cursor name> cursor for select <Alpha field1>,<Alpha field2>,<Numeric field1> from deleted
    open <Cursor name>
    fetch next from <Cursor name> into @sk1,@sk2,@nk1
    WHILE (@@FETCH_STATUS <> -1)
    begin
        SELECT @realhead = HEADER,@maximum = MAXI FROM HEAD
        SELECT @in_table = MAX(IND) FROM TRB
        set @realhead=@realhead+1
        IF @realhead>@maximum 
        BEGIN
            set @realhead=1
        END
        IF @in_table<@realhead 
        BEGIN
            INSERT INTO TRB (IND,TABL,EVENT,SK1,SK2,SK3,SK4,SK5,NK1,NK2,NK3,NK4,NK5) values
            (@realhead,'<Table name>','3',@sk1,@sk2,NULL,NULL,NULL,@nk1,NULL,NULL,NULL,NULL)
            UPDATE HEAD SET HEADER=@realhead
        END 
        ELSE
        BEGIN
            UPDATE TRB SET IND=@realhead,TABL='<Table name>',EVENT='3',SK1=@sk1,
            SK2=@sk2,SK3=NULL,SK4=NULL,SK5=NULL,NK1=@nk1,NK2=NULL,NK3=NULL,NK4=NULL,NK5=NULL WHERE IND=@realhead;
            UPDATE HEAD SET HEADER=@realhead;
        END
        fetch next from <Cursor name> into @sk1,@sk2,@nk1
    end
    close <Cursor name>
    deallocate <Cursor name>
    commit 
END

GO