Re: creating procedure for audit table

Thank's Jonathan, I just sorry for my late reply, i just back from my summer vacation. Honestly, i not so good with informix procedure. Maybe you have this kind procedure and trigger that running well ? so I'll grateful if you pass it to me. again thank's and appreciated...! rgds Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<3EEEA3D6.1040007@earthlink.net>... > 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.