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?

asked 28 Jul '16, 10:04

BlueMark's gravatar image

BlueMark
24691523
accept rate: 50%


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%20Culp's gravatar image

Mark Culp
23.2k9132272
accept rate: 40%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×60
×36
×31
×24
×3

question asked: 28 Jul '16, 10:04

question was seen: 243 times

last updated: 29 Jul '16, 03:32