StoredProcedures

Examples of stored procedures and triggers

A lot of people on the mailing list are missing examples of triggers and stored procedures. If you got some to work, just post them here with a short explanation.

Loading a trigger

This is how I load triggers

repmcli -d MYDB -u ME,SECRET -b trigger.sql

Send email to Fabian

Keeping track of article supply with triggers

There's a table Articles with all the general information about an article. The field Supply holds the number of articles in stock. The table Stock contains incarnations of the articles with serial number, etc.. Whenever an item is inserted in Stock the Supply of the corresponding article in Article is increased. When the field Sold of an item in Stock is changed, the supply changed accordingly. Finally when an item is deleted from Stock the supply is decreased if this item was not sold yet. This way we could indicate whether an article is available without a complicated join with the table Stock.

/
CREATE TRIGGER Stock_Insert FOR shop.Stock AFTER INSERT EXECUTE
BEGINPROC
UPDATE hrzshop.Article SET Supply = Supply + 1 WHERE oid = :NEW.Article;
IF $rc <> 0 THEN STOP($rc);
ENDPROC;
/
/
CREATE TRIGGER Stock_Update FOR shop.Stock AFTER UPDATE (Sold) EXECUTE
BEGINPROC
IF NEW.Sold <> 0 
THEN UPDATE hrzshop.Article SET Supply = Supply - 1 WHERE oid = :NEW.Article
ELSE UPDATE hrzshop.Article SET Supply = Supply + 1 WHERE oid = :NEW.Article;
IF $rc <> 0 THEN STOP($rc);
ENDPROC;
/
/
CREATE TRIGGER Stock_Delete FOR shop.Stock AFTER DELETE EXECUTE
BEGINPROC
UPDATE hrzshop.Article SET Supply = Supply - (1 - :OLD.Sold) WHERE oid = :OLD.Article;
IF $rc <> 0 THEN STOP($rc);
ENDPROC;
/

Send email to Fabian

Stored procedure to create a row version control trigger

Reasoning: As a game developer we use a SAP DB database to handle much of our game data, esp. balancing stuff like weapon stats, units levels, equipment values and lot's of other stuff. We consider these data as source code. Each change to this data requires a QA testing pass, since the whole balancing of the game can be wrecked by changing a number. So we need control of who changed when what values to plan our QA resources. To accomplish this we had two opportunities:

1. All changes are done through stored procedures which write them into a log table. This would make the use of RAD tools like Delphi for the clients nearly impossible.

2. Create a post update trigger on every table to watch for changed data. This integrates seamless into our current environment.

We went with option 2. To make the creation of the trigger easier, we wrote a stored procedure which analyses a table and returns the trigger to the caller. This is necessary since we cannot create the trigger from inside the stored procedure yet (there was some support for dynamic sql promised for the future).

CREATE DBPROC VERSIONCONTROL_CREATE (IN TABLENAME CHAR(64) , OUT TRIGGER CHAR(7000)) AS
    VAR TABLE CHAR(64);
        COLUMN CHAR(64);
        DATATYPE CHAR(64);

    SET TABLE = UPPER(TABLENAME);

    
    SET TRIGGER = 'create trigger versioncontrol_' & TABLE & ' for  ' & TABLE & ' after update execute (';
    SET TRIGGER = TRIGGER & 'var log char (2048);';
    SET TRIGGER = TRIGGER & 'set log = '''';';
    
    DECLARE COLS CURSOR FOR SELECT COLUMNNAME, DATATYPE FROM DOMAIN.COLUMNS WHERE TABLENAME = :TABLE;
     FETCH FIRST COLS INTO :COLUMN, :DATATYPE;
     WHILE $RC = 0 DO
     BEGIN
         IF DATATYPE <> 'LONG' THEN
         BEGIN
             SET TRIGGER = TRIGGER & 'if old."' & COLUMN & '" <> new. "' & COLUMN & '" then set log = log & '' column changed: ' & COLUMN & ''';';
         END;

         FETCH NEXT COLS INTO :COLUMN, :DATATYPE;
     END;
     
     CLOSE COLS;

    SET TRIGGER = TRIGGER & 'insert into dba.changelog (table, description) values (''' & TABLE &''', :log);';
    SET TRIGGER = TRIGGER & ')';

The next updates of the trigger will probably do the following:

• write out what the old and the new values of the column are

• enhance the log table from just a description to a system where the old and new values are stored in SQL types

Dirk Ringe

Download some example scripts

While porting from Oracle I really had a hard time figuring out the SAPDB stored procedure syntax. (why have "Oracle mode" if you cant do both tables, triggers and procedures?)

Then I found this project: http://sourceforge.net/projects/osdldbt just download one of their releases, and you'll have a lot of examples to look at.

Peter

Next example...

place your example here good