SQL Anywhere database - Design advice for LOG of changes

Author

Design advice for LOG of changes

Edgard L. Riba

2005-06-14, 1:23 pm

Hi,

I need to develop a log of changes to selected tables. The requirement
only calls to record the primary key (an integer), the timestamp of the
event, the user id (another integer), and whether the row was
added/changed/deleted.

My initial idea is to write an AFTER UPDATE, INSERT, DELETE FOR EACH ROW
trigger, for each table which requieres this, and write an entry to a "LOG"
table.

What do you guys think of this design?

Best regards and thanks in advance,
Edgard L. Riba

Jeff Albion $iAnywhere Solutions$

2005-06-14, 1:23 pm

First off, before you invest some time in development, you may want to
investigate usage of the Auditing utility that exists in the server already.
It performs automatic logging by making notes in the transaction log, which
you can later extract using dbtran. Be sure to read about it at:

- SQL Anywhere Studio Security Guide
- Keeping Your Data Secure
- Auditing database activity

Outside of that, if you want to keep to your original idea, you most likely
will want to create row-level triggers rather than triggers at
statement-level.

If you are using Transact-SQL, (which operates at a statement level basis)
to log changes to individual rows, you will need to have a scrolling cursor
that loops through the result set. If it's coded in Watcom-SQL, be sure that
it is row-level based.

--
Jeff Albion
Product Support Analyst
iAnywhere Solutions
jalbion@sybase.com
+1 (519) 883-6266

"Edgard L. Riba" <elriba at rimith dot com> wrote in message
news:42af195c$1@foru
ms-1-dub...
> Hi,
>
> I need to develop a log of changes to selected tables. The requirement
> only calls to record the primary key (an integer), the timestamp of the
> event, the user id (another integer), and whether the row was
> added/changed/deleted.
>
> My initial idea is to write an AFTER UPDATE, INSERT, DELETE FOR EACH ROW
> trigger, for each table which requieres this, and write an entry to a
"LOG"
> table.
>
> What do you guys think of this design?
>
> Best regards and thanks in advance,
> Edgard L. Riba
>
>

Scott Holman

2005-06-15, 7:23 am

This is an effective design.

"Edgard L. Riba" <elriba at rimith dot com> wrote in message
news:42af195c$1@foru
ms-1-dub...
> Hi,
>
> I need to develop a log of changes to selected tables. The requirement
> only calls to record the primary key (an integer), the timestamp of the
> event, the user id (another integer), and whether the row was
> added/changed/deleted.
>
> My initial idea is to write an AFTER UPDATE, INSERT, DELETE FOR EACH ROW
> trigger, for each table which requieres this, and write an entry to a
> "LOG" table.
>
> What do you guys think of this design?
>
> Best regards and thanks in advance,
> Edgard L. Riba
>

Edgard L. Riba

2005-06-15, 9:23 am

Hi Jeff,

Thanks for responding.

I wasn't aware of that feature. Thanks.

The problem I have is that my users want to do the auditing, so I need
something that is online and that I can include a module in my application
for viewing.

I have one question -out of curiosity- regarding TransactSQL and it not
supporting row level triggers. Does this mean that Sybase Enterprise
doesn't support row level triggers?

Thanks,
Edgard

"Jeff Albion (iAnywhere Solutions)" <jalbion@sybase.com> escribió en el
mensaje news:42af2004@forums
-1-dub...
> First off, before you invest some time in development, you may want to
> investigate usage of the Auditing utility that exists in the server
> already.
> It performs automatic logging by making notes in the transaction log,
> which
> you can later extract using dbtran. Be sure to read about it at:
>
> - SQL Anywhere Studio Security Guide
> - Keeping Your Data Secure
> - Auditing database activity
>
> Outside of that, if you want to keep to your original idea, you most
> likely
> will want to create row-level triggers rather than triggers at
> statement-level.
>
> If you are using Transact-SQL, (which operates at a statement level basis)
> to log changes to individual rows, you will need to have a scrolling
> cursor
> that loops through the result set. If it's coded in Watcom-SQL, be sure
> that
> it is row-level based.
>
> --
> Jeff Albion
> Product Support Analyst
> iAnywhere Solutions
> jalbion@sybase.com
> +1 (519) 883-6266
>
> "Edgard L. Riba" <elriba at rimith dot com> wrote in message
> news:42af195c$1@foru
ms-1-dub...
> "LOG"
>
>

Jeff Albion $iAnywhere Solutions$

2005-06-15, 8:24 pm

It actually means that when you have a statement in ASE that fires a trigger
and the statement returns multiple rows, you must loop through it using
Explicit row-level looping or using cursors for Cursor row-level looping.

See http://www.sybase.com/detail?id=1355 for more detail.

--
Jeff Albion
Product Support Analyst
iAnywhere Solutions

"Edgard L. Riba" <elriba at rimith dot com> wrote in message
news:42b02ebf$1@foru
ms-1-dub...
> Hi Jeff,
>
> Thanks for responding.
>
> I wasn't aware of that feature. Thanks.
>
> The problem I have is that my users want to do the auditing, so I need
> something that is online and that I can include a module in my application
> for viewing.
>
> I have one question -out of curiosity- regarding TransactSQL and it not
> supporting row level triggers. Does this mean that Sybase Enterprise
> doesn't support row level triggers?
>
> Thanks,
> Edgard
>
> "Jeff Albion (iAnywhere Solutions)" <jalbion@sybase.com> escribió en el
> mensaje news:42af2004@forums
-1-dub...
basis)[color=darkred
]
requirement[color=da
rkred]
ROW[color=darkred]
>
>

Breck Carter [TeamSybase]

2005-06-15, 8:24 pm

On 15 Jun 2005 06:35:59 -0700, "Edgard L. Riba" <elriba at rimith dot
com> wrote:

>Does this mean that Sybase Enterprise
>doesn't support row level triggers?

That is correct.

(I would call SQL Anywhere an "enterprise" product, but I know you're
asking about ASE :)

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links