Re: Trigger Translation from Oracle and PostgrSQL

[maxdb]

Simple trigger calling a store procedure to do the work
This two scripts new obviously two tables named "companias" and other 
named "periodos_contables"

create trigger companias_trigger for companias after insert execute (
    Call dba.sp_conta_addPeriodoContable(:NEW.ID_Compania, 'blah blah');
)
/*/
create dbproc dba.sp_conta_addPeriodoContable(
    in intID_Compania integer,
    in strDescripcion varchar(50)
) as
/*
    Insertar un periodo contable
*/
Var intID_Periodo integer;
Begin
    Try
        Set intID_Periodo = 0;
        Select
          Case Max(ID_Periodo)
            When Null Then 1
            Else Max(ID_Periodo)+1
          End Into :intID_Periodo
        From dba.Periodos_Contables Where ID_Compania=:intID_Compania;
        If intID_Periodo=null Then
            Set intID_Periodo = 1;
        Insert Into dba.Periodos_Contables (ID_Periodo, ID_Compania, 
Descripcion)
        Values (:intID_Periodo, :intID_Compania, :strDescripcion);
    Catch
        Stop($RC, $ERRMSG);
End;


Alexander Papadopulos wrote:
Hello everybody,
I desperately need documentation on MaxDB Triggers.
I have the reference manual, I need something more basic with
examples.
I have to translate Oracle PL/SQL and PostgreSQL PL/PGSQL Triggers to
MaxDB. 

Here is a PL/PGSQL Trigger, if anyone has the skill and time to
translate this I would be forever grateful. It is a temporal integrity
constraint for a valid time table. The development of the annual
salary of an employee through time in recorded. An employee identified
by NAME has a SALARY for a specific period of time from START to END.
The periods of time should not overlap.
CREATE TRIGGER seq_key AFTER INSERT OR UPDATE ON salary
FOR EACH ROW EXECUTE PROCEDURE seq_key_salary();
CREATE OR REPLACE FUNCTION seq_key_salary() RETURNS OPAQUE AS '
DECLARE
  VALID INTEGER;
BEGIN
IF (
 EXISTS (
  SELECT * FROM SALARY AS S1 WHERE 1 < ( 
   SELECT COUNT(NAME) FROM SALARY AS S2 
   WHERE S1.NAME = S2.NAME 
   AND (S1.START, S1.END) OVERLAPS (S2.START, S2.END)
   )
  )
 )
OR EXISTS ( 
 SELECT * FROM SALARY AS S WHERE S.NAME IS NULL
 ) 
THEN RAISE EXCEPTION '' SEQUENCED PK CONSTRAINT VIOLATED '', NEW.NAME;
END IF;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql';

--------
Table gehalt looks like this:
CREATE TABLE SALARY(
 NAME VARCHAR,
 SALARY INTEGER,
 START DATE,
 END DATE,
 PRIMARY KEY (NAME, START)
)
-------
Thank you for reading this.
Alexander

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]