> I try to setup a trigger but fail to figure out the right way, so I >am asking for your help. > > I have the table: > >create table orx ( > id serial primary key, > h fixed(10), > val int, > avgt int ) > >fo user dbo. In orx.h i store the time in unix time format (seconds since epoch ...), >in val a value and in avgt the type of average val is. When I insert a new value (val) >I need to know how much time have passed since last avgt of let's say 1 (an average >at >1800 seconds). If 1800 seconds passed from the last avgt1 insert I need to >recalculate >a new one and insert the value into the table. > >I try to set up a trigger witch shows if it is the right time to insert a new >average. But it returns >-24988,ERR_SQL: sql error >-7045,Parameter spec not allowed
>CREATE TRIGGER mkavg FOR dbo.orx AFTER INSERT EXECUTE >( >VAR lasth FIXED(10); >TRY > SELECT h FROM dbo.orx WHERE avgt LIKE 1; > FETCH LAST INTO :lasth; > IF ( :new.h - :lasth ) => 1800 THEN EXIT; >CATCH > STOP ($rc, $ERRMSG); >) > > > Can you tell what am I doing wrong, and also if this average trigger can be >done in sapdb, where should I find the fine manuals to read them ? Please change SELECT h FROM dbo.orx WHERE avgt LIKE 1; ... IF ( :new.h - :lasth ) => 1800 THEN EXIT; to SELECT h FROM dbo.orx WHERE avgt = 1; ... IF ( h - lasth ) >= 1800 THEN RETURN; Explanation : 1. NEW/OLD is only allowed in UPDATE triggers. 2. Parameters are needed in SQL statements only. In IF statements no parameters must be used. 3. leave the trigger via the RETURN statement. I didn't understand what you want to do if more than 1800 seconds have passed. If you want the triggering insert statement to fail, you must set an error. In this case please substitute the RETURN statement by a STOP statement : IF ( h - lasth ) >= 1800 STOP (-31000, 'more than 1800 seconds'); Regards, Thomas -- Thomas Anhaus SAP DB, SAP Labs Berlin [EMAIL PROTECTED] http://www.sapdb.org/ _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
