Hi,

> I'm trying to write a trigger that log to a table all user operations.
>
> So I write someting like this:
> CREATE TRIGGER table1_log FOR table1
> ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0
> AS
> BEGIN
> IF (USER <> 'REPL') THEN BEGIN
> INSERT INTO table_log(utente, tipo, tabella, istruzione)
> VALUES (USER,

I don't think the CASE expression is supported here.

Why not declare a variable of type CHAR(1) and do:

IF (inserting)
THEN myvar = 'I'
ELSE IF (updating)
THEN myvar = 'U'
ELSE IF (deleting)
THEN myvar = 'D';

:myvar,

> 'TABLE1',
> case
> when (inserting) then
> 'INSERT INTO table1 (
> fieldkey,
> field1,
> field2)
> VALUES
> ('||new.fieldkey||','||new.field1||','||new.field2||')'
> when (updating) then
> 'UPDATE table1 SET fieldkey='||new.fieldkey
> || case when (new.field1#old.field1) then
> ',field1='||new.field1|| end
> || case when (new.field2#old.field2) then
> ',field2='||new.field2|| end
> || ' WHERE fieldkey=old.fieldkey'
> when (deleting) then
> 'DELETE FROM table1 WHERE fieldkey=old.fieldkey'
> end);END
> end
>
> but in the (updating) CASE, when I try to insert another CASE IBExpert
> return a precompiler error.
>
> My question is:
>
> It is possible to annidiate CASE statement? If not how can I write my
> trigger to obtain same result?

Use IBLogManager to avoid all this ;-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com