Database Reference
In-Depth Information
to the trigger are as expected. For example, it is easy to see that if you omit the FOR
EACH ROW part, the TD['old'] and TD['new'] will be both empty as the trigger
definition defaults to FOR EACH STATEMENT .
A log trigger
Now, we can put this knowledge to work and write a trigger that logs changes to the
table to either a file or to a special log-collector process over UDP. Logging to a file
is the simplest way to permanently log the changes in transactions which were rolled
back. If these were logged to a log table, the ROLLBACK command would also re-
move the log records. This may be a crucial audit requirement for you business.
Of course, this also has the downside. You will be logging the changes that may not
be permanent due to the transaction being rolled back, but this is the price you have
to pay for not losing the log records.
CREATE OR REPLACE FUNCTION log_trigger()
RETURNS TRIGGER AS $$
args = tuple(TD["args"])
if not SD.has_key(args):
protocol = args[0]
if protocol == 'udp':
import socket
sock = socket.socket(
socket.AF_INET,
socket.SOCK_DGRAM )
def logfunc(msg, addr=args[1],
port=int(args[2]),
sock=sock):
sock.sendto(msg, (addr, port))
elif protocol == 'file':
f = open(args[1], 'a+')
def logfunc(msg,f=f):
f.write(msg+'\n')
f.flush()
else:
raise ValueError, 'bad logdest in
Search WWH ::




Custom Search