ORACLE trigger/PLSQL question...hmm

[Spud1]

Ok i'm not very experienced with oracle triggers,PL/SQL etc but I am learning, so bear with me

I have 2 tables involved in this situation, a source tabled called:

'dm_audittrail_s' and a destination table called 'rds_monitoring'

What I want to do is have a trigger on the dm_audittrail_s table to fire when a new record is inserted, and for that trigger to insert a new record on rds_monitoring. Simple yes? Well it gets more complex.
I want the trigger to only fire when a certain type of record is inserted - that is, there is an attribute in dm_audittrail_s called 'EVENT_NAME', which could be a variety of things but is always a string. I only want the trigger to fire when EVENT_NAME = 'dm_getfile'

I'm struggling to get it to do that at the moment ;/

this is the code that I have currently (abridged to only the relavent code)

CREATE OR REPLACE TRIGGER rds_monitor_update
	AFTER INSERT ON dm_audittrail_s

	REFERENCING NEW AS newRow

	FOR EACH ROW
	WHEN (newRow.EVENT_NAME='dm_getfile')

             DECLARE <etc etc> BEGIN <etc etc> EXCEPTION <etc etc> END

The trigger is currently fireing on any event - not just dm_getfile

anyone got any ideas at all?

Failing that any links to sites with lots of detail on triggers / plsql would be helpful, particularly with info about the when clause and the for each row clause (the oracle documentation is good, but could use more insight..)

Cheers in advance for any help

Spud/Pete

[TiG]

One thing i will say is that if this is firing a large number of events then a trigger (at least in my opinion) is not the way to implement this. Triggers when used on a large scale absolutely hammer DB performance and can cause problems further down the line.

Its not possible to alter the Stored Proc's or SQL code to add the insert instead of writing this conditional trigger, that will always need to be evaluated?.

TiG

[Moby-Dick]

wo0ho0 1st dev post I've actually understood

I'm with TiG on this - lots of triggers is bad ( from a DBa point of view )

[Spud1]

Sorry maybe i'm not being as clear as I should be

I can't go into too much detail for various reasons but anyway, it's not lots of triggers, there's only one. All this trigger will do is update a different table when a record is inserted into dm_audittrail_s with the value 'dm_getfile' in the EVENT_NAME attribute/field/whatever you want to call it

The dm_audittrail_s table is a table to store information about a user and what the use r is doing when they access certain files on our content management system (a customised version of documentum if that means anything to you )

does that clear it up a bit?

[nick_1]

The best example I have found that is similar is this (from http://www-db.stanford.edu/~ullman/f...igger%20syntax):

CREATE TRIGGER trig1
    AFTER INSERT ON dm_audittrail_s
    REFERENCING NEW AS newRow

    FOR EACH ROW
    WHEN (newRow.EVENT_NAME='dm_getfile')
    BEGIN
        ...blah...;
    END trig1;
.

But that does look worryingly like your code (i tweaked the table names to match). Are you sure the compare is correct? I've had a few oddities in SQL Server where EVENT_NAME = null returned true as well. The system I work on has implemented something similar using a call to another sp within each stored proc which inserts into that table.

I hope this has helped.
Nick

[Spud1]

Thanks for the suggestions

It turns out that my code was correct, it was a problem with the application providing the EVENT_NAME thing, causing it to mis-fire
anyway its sorted now ty

[jonneymendoza]

what is "fire" trigger? never came across that in sql b4, im curious 2 know what it does

[nick_1]

what is "fire" trigger? never came across that in sql b4, im curious 2 know what it does

Triggers allow you to make SQL more event driven. It does have the side effect of slowing down inserts/updates... basically whatever they are tied to. I would generally recommend avoiding them unless you really have to. A better design is to use stored procedures and include whatever extra code you need within a transaction.

Have a look at the link i posted earlier for more details.

Nick

[Spud1]

By 'Fire' Trigger i mean launch it, execute it, whatever you want to call it, it basically means running it when something happens (an insert, update etc etc). Fire is just a commonly used term to describe it.

[jonneymendoza]

o i see so basicly the database or whatever, will do an operation by itself without a user interacting with the database using something that triggers the operation? like for example add a new record to a relational table automaticly when somthing else happens in say another relation table..

ahh makes sence

[Ramedge]

hey, iam not very good at SQL nor do i know much about Oracle, but my pa is a genius when it comes to this stuff! hehe
he said the best person to speak to is this american called Tom whom he has met before, this guy knows every thing there is to know and will answer your questions personally. My dads students use him as one of their main resources when working with ORACLE,
http://asktom.oracle.com/
sorry if you already know about this, just thought it may be of some help

[jonneymendoza]

i would say i know quit abit of sql, nice link though, will be visiting that quite often

[happytechie]

you could aslso try the forums at technet.oracle.com or metalink.oracle.com (Oracle support contract required)

HT