Re: creating procedure for audit table

abdoell wrote: > I'm running informix 9.30UC4W3 on Solaris 7/8. > I need your help as i'm new for informix to create a procedure. > This procedure purpose to audit a specific table if this table hit by > someone (UPDATE) and another procudure to denied any UPDATE on the > table. Beware: it is difficult to reliably auudit actions when the actions can be rolled back. Consider RAW tables for your audit log. Also, be very careful with the permissions on the audit log tables. > I have created but not working. > > Could someone pls to help me to create a working procedure to LOG > every update and to DENIED any update to specific table. > BRGDS > ABDOELL > > my procedure... > create table .audit_table > ( > login char(16) not null , > atime datetime year to minute not null , > table char(20) not null , > type char(20) not null , > change char(300) not null > ); Not all changes will be just 300 characters long, in general. Which operations recorded in type will be as long as 20 characters? Do you need before images, after images, or both? Note that before images are tricky for inserts, after images for deletes. > and create triggers and procedures for each table that I need an audit > trail for. > > create table intrud > ( > a integer, > b char(8) > ); > revoke all on intrud from "public";> > > > create trigger update_intrud insert on inrud referencing on intrud referencing? > new as post_upd > for each row > ( > execute procedure audit_table(post_upd.a ,post_upd.b > ));> > CREATE PROCEDURE audit_table(a int, b char(8))> define change char(80); > > insert into audit_table values (user, current, "intrud", "INSERT",> change); > END PROCEDURE; > let change = a || "," || b; Why is the END PROCEDURE before the LET change? Why is the INSERT before the LET change? Note that your change values are ambiguous, in general (though not as bad as if you'd used TRIM()). You've not even attempted the reject/deny part. If you reject an update, then the statement as a whole gets rolled back, which means in turn that any logging actions get rolled back. However, in a logged database, if a triggered procedure raises an exception, then the statement is rolled back. Unlogged databases play by different rules - don't use them. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/