Hi All!

I want to log every transaction on some tables, with one single procedure.
The procedure will be called like this:
execute procedure naploz(<TABLENAME varchar(20)>,<TRANS.TYPE char(1) -
'I' for insert, 'u' for update, 'd' for delete>)
How can I retrieve the OLD.<FIELDNAME> and NEW.<FIELDNAME> values?

A trigger:

CREATE TRIGGER SYSTEM_AD_NAPLOZ FOR SYSTEM
ACTIVE AFTER DELETE POSITION 0
AS
begin
EXECUTE PROCEDURE NAPLOZO('SYSTEM','D');
end

The procedure:

CREATE PROCEDURE NAPLOZO (
RELNEV VARCHAR(20),
DELET_E CHAR(1))
AS
DECLARE VARIABLE NAPLOFIELD VARCHAR(20);
DECLARE VARIABLE NAPLOFIELDS VARCHAR(300);
DECLARE VARIABLE NAPLODAT VARCHAR(400);
DECLARE VARIABLE STR VARCHAR(100);
DECLARE VARIABLE TIPSTR VARCHAR(6);
DECLARE VARIABLE C INTEGER;
DECLARE VARIABLE SYSDATUM DATE;
DECLARE VARIABLE SYSIDO DATE;
DECLARE VARIABLE USERNUM INTEGER;
begin
str='';
naplofield='';
naplofields='';
naplodat='';
c=0;
SELECT count(rdb$field_name) FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME=:relnev
into c;
FOR SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME=:relnev
into :naplofield
do
begin
if (c>1) then
begin
naplofields=naplofields||:naplofield||',';
if (delet_e='D') then
begin
naplodat=naplodat||'OLD.'||:naplofield||'#';
end
else begin
naplodat=naplodat||'NEW.'||:naplofield||'#';
end
end
else begin
naplofields=naplofields||:naplofield;
if (delet_e='D') then
begin
naplodat=naplodat||'OLD.'||:naplofield;
end
else begin
naplodat=naplodat||'NEW.'||:naplofield;
end
end
c=c-1;
end
-- naplodat=CAST('||:naplodat||'as VARCHAR(400))';
str='';
if (delet_e='D') then tipstr='DELETE';
if (delet_e='I') then tipstr='INSERT';
if (delet_e='U') then tipstr='UPDATE';
SELECT DATUM from GETDATUM
into :SYSDATUM;
SYSIDO = 'NOW';
USERNUM='0'; --jav: gepnev+user
INSERT into syslog
(TABLES,FIELDS,FIELDVALUES,USERNUM,SYSDATUM,SYSIDO,TRANS)
values (:relnev,:naplofields,:naplodat,:USERNUM,:SYSDATUM,:SYSIDO,:tipstr);
suspend;
end

Thank You for Your help in advance.

--

Laszlo KOVACS
eMail: kovacslaszlo@...
Mobile: +36 30 9895 228
NeophoneX: 707113
Skype: tiesoft
MSN: kovacslaszlo@...
ICQ: 168868701
YAHOO: kovacslaszlo002
AIM: tiesoft
Firefly: 89790505

---