db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre.Tjeldv...@Sun.COM
Subject Re: using SELECT as an SQL triggered action
Date Tue, 12 Feb 2008 08:59:39 GMT
musky <muskvar@gmail.com> writes:

> somebody please help me here please!!
> please!!
> its urgent...

Please see 
http://www.catb.org/~esr/faqs/smart-questions.html#urgent

>
> musky wrote:
>> 
>> i will make my aim more clearer here:
>> i have created an AFTER insert trigger on a table called chattable which
>> has two fields(ID INT,Message VARCHAR(255)).here is the trigger:
>> "CREATE TRIGGER insert_trigger AFTER INSERT ON chattable REFERENCING
>> NEW_TABLE AS chattableupdate FOR EACH STATEMENT SELECT * FROM
>> chattableupdate"
>> where chattableupdate is a transition table.
>> 
>> i want the transition table to contain the newly inserted rows whenever an
>> insert takes place into chattable.
>> 
>> please help me,because with this trigger,when i select * from
>> chattableupdate and assign it to a resultset i get an error that the
>> "chattable view/table does not exist"

I think it would be a lot easier to figure out what the problem is if
you could show the failing code.

>>>> my problem is how to store the result of the SELECT * from table_name
>>>> statement so that i can display it on the console output whenever ant
>>>> data
>>>> is inserted into the table,that data is retrieved from the table.
>>> 
>>> It sounds like you're trying to build a sort of "audit trail", so that
>>> you can keep track of changes to your data.
>>> 
>>> Triggers are indeed useful for this sort of thing.
>>> 
>>> I think one way to do this is to have your trigger capture the data
>>> that is being inserted/updated/deleted, and inside your trigger take
>>> that data and store it in an "audit" table.
>>> 
>>> Then, whenever you want, you can issue normal select statements against
>>> your audit table, and fetch the audit records of interest.
>>> 
>>> thanks,
>>> 
>>> bryan

-- 
dt

Mime
View raw message