"Zabach, Elke" wrote: > > Or maybe isn't it possible to use triggers and/or stored procedures ? > ==> triggers are a good idea and as far as I remember one member > of this list did it before, but I don't remember his name. > Can he tell us something about his experiences?
Well, that would be me, I'm using triggers on insert, update and delete on every table to insert into a table. I do this to be able to quickly invalidate cached objects in my applications, the changelog table contains: an id: Sequentially generated id. object id: really a string that contains the tablename and the primary key. event: Insert, Delete, Update As I use this purely for cache invalidation I don't log who did the change. Remember to not add these triggers to the changelog table or you will be in trouble. These triggers are all generated from the database schema and they could look like this: SQL: CREATE TRIGGER "ticket_insert" FOR "ticket" AFTER INSERT EXECUTE ( TRY INSERT INTO TEST.changelog (changelog_id,event,oid) values (CHANGELOG_id_sequence.nextval,'I','ticket:'&RTRIM(:NEW.TICKET_ID)); CATCH IF $rc <> 100 THEN STOP ($rc, 'In trigger ticket_insert: '&$errmsg); ) SQL: CREATE TRIGGER "ticket_update" FOR "ticket" AFTER UPDATE EXECUTE ( TRY INSERT INTO TEST.changelog (changelog_id,event,oid) values (CHANGELOG_id_sequence.nextval,'U','ticket:'&RTRIM(:OLD.TICKET_ID)); CATCH IF $rc <> 100 THEN STOP ($rc, 'In trigger ticket_update: '&$errmsg); ) SQL: CREATE TRIGGER "ticket_delete" FOR "ticket" AFTER DELETE EXECUTE ( TRY INSERT INTO TEST.changelog (changelog_id,event,oid) values (CHANGELOG_id_sequence.nextval,'D','ticket:'&RTRIM(:OLD.TICKET_ID)); CATCH IF $rc <> 100 THEN STOP ($rc, 'In trigger ticket_delete: '&$errmsg); ) -- Regards Flemming Frandsen aka. Dion/Swamp http://dion.swamp.dk _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
