SAP SQL Anywhere

Question

Is this possible to create trigger like this with SQL Anywhere 12?

CREATE TRIGGER "SysProcLogChanges" after insert order 9
on sys.SYSPROCEDURE
referencing new as newvalue
for each row
begin

    insert into "DBA"."ProcLog" ("name","definiton","time") 
    select newvalue.proc_name, newvalue.proc_defn, now();

end;

I would like to monitor all changes on procedures and functions. Or maybe there is another solution to this than trigger? Some event?

Accepted Answer

1

You cannot create/add triggers to any of the system tables, so no, you cannot do what you are proposing. This is true in all versions of SQL Anywhere.

One method to do what you want is to do all updates to your procedures and functions by using a procedure and then within that procedure log the update after performing the update to the system catalog.

HTH

permanent link

answered 28 Jul '16, 10:10

Mark Culp
24.9k●10●141●297
accept rate: 41%

Any other way to monitor that something changed in procedures / functions? I was thinking to create another table and fill it with content of sys.SYSPROCEDURE - in this way I can create some event to monitor every one minute, but maybe there is something better to discover changes directly at the moment when change was made? I would like to log also db user and other information from connection who made changes - with event this will be not possible :/

(28 Jul '16, 10:27) BlueMark

Replies hidden

1

Similar questions have been asked here, you may search for "DDL trigger" (as that is what it's called for MS SQL Server AFAIK), answers include

using a particular STP to execute and log any DDL statements (like Mark has suggested),
using DBTRAN to look for such statements post-mortem,
using auditing (with type DDL) to collect information who and when a DDL statement was executed.

Unfortunately SYSPROCEDURE does not have a "last_modified_at" column like SYSTAB has, which otherwise might have been helpful to discover changes (at least after a checkpoint).

With v16 and above, you might take influence via controlling the ALTER ANY PROCEDURE / CREATE ANY PROCEDURE system privileges and the like.

(28 Jul '16, 11:11) Volker Barth

2

SYSPROCEDURE changes are recorded in the transaction log. The dbtran utility can be used to read and translate transaction log entries into readable text. The dbtran -c option can be used to connect to the database to read the active transaction log. Alternatively, the transaction log file name can be specified if you want dbtran to read an old transaction log.

The truth is out there, that's the good news. Scanning and interpreting the output from dbtran, that's [cough] another matter :)

(29 Jul '16, 03:32) Breck Carter

flat view

SAP SQL Anywhere

Trigger to log changes in procedures and functions

Follow this question

Related questions