Home » SQL & PL/SQL » SQL & PL/SQL » create trigger on database
create trigger on database [message #257512] Wed, 08 August 2007 09:08 Go to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
hi all
iuse database 10g on windows 2003.
how can i create trigger on datebase fire when
insert or update on any table in databse .
how can i do it ?
thax
Rafeek

[Updated on: Wed, 08 August 2007 09:13]

Report message to a moderator

Re: create trigger on database [message #257513 is a reply to message #257512] Wed, 08 August 2007 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>how can i do it ?
By using vi or editor of choice.
Re: create trigger on database [message #257518 is a reply to message #257512] Wed, 08 August 2007 09:23 Go to previous messageGo to next message
jaagira
Messages: 9
Registered: August 2007
Junior Member
I guess he meant global trigger on any table for insert/update. I don't think there is such feature exist in Oracle..And I don't see any use of it anyway...How can one common trigger will help for all table?very goofy... No idea..Can you describe your requirement?
Re: create trigger on database [message #257553 is a reply to message #257512] Wed, 08 August 2007 11:18 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Have a look at enabling auditing, not sure what your needs are, but maybe that would suffice.
Re: create trigger on database [message #257566 is a reply to message #257553] Wed, 08 August 2007 11:57 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
http://www.adp-gmbh.ch/ora/sql/create_trigger.html
Re: create trigger on database [message #257604 is a reply to message #257512] Wed, 08 August 2007 14:06 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

Try FGA. If you are using 10gXE, you can't use it. otherwise Fine-Grained Auditing can help you. Anyway... speak to your DBA. Even after all you couldn't solve.. let me know

[Updated on: Wed, 08 August 2007 14:07]

Report message to a moderator

Re: create trigger on database [message #257607 is a reply to message #257604] Wed, 08 August 2007 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For FGA you have to define a trigger on each table.

Regards
Michel
Re: create trigger on database [message #257610 is a reply to message #257607] Wed, 08 August 2007 14:44 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

hmm... I was mistaken Michel...

can we try Flashback Query
Re: create trigger on database [message #257612 is a reply to message #257610] Wed, 08 August 2007 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The question was:
Quote:
how can i create trigger on datebase fire when
insert or update on any table in databse .
how can i do it ?

What is the relation with flashback query?

Regards
Michel
Re: create trigger on database [message #257676 is a reply to message #257612] Thu, 09 August 2007 00:19 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

well, We can run a trigger whenever Flashback updated. I know, it will update for all operations (not only Insert or Update).

Initially i will store Flashback current Version Id. I will store that id in my table. I will update my table whenever Version has updated. Whenever my table updated i can track i guess...

Can we try with this way Michel??
Re: create trigger on database [message #257687 is a reply to message #257676] Thu, 09 August 2007 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I don't understand a word of what you posted, I even can't imagine what you trying to do.

First, what is the meaning of?
Quote:
We can run a trigger whenever Flashback updated


Regards
Michel
Re: create trigger on database [message #257772 is a reply to message #257687] Thu, 09 August 2007 02:44 Go to previous messageGo to next message
rajvaida
Messages: 26
Registered: May 2006
Location: India
Junior Member

http://www.psoug.org/reference/flash_version.html

got any clue?
Re: create trigger on database [message #257789 is a reply to message #257772] Thu, 09 August 2007 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A clue of what?
I know what is flashback, I wrote a 60 pages article on Oracle flashback technology but I don't understand what you said.
Can you explain?

Regards
Michel
Re: create trigger on database [message #257981 is a reply to message #257676] Thu, 09 August 2007 13:26 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
i can not understant u bout
{{"Initially i will store Flashback current Version Id. I will store that id in my table. I will update my table whenever Version has updated. Whenever my table updated i can track i guess..."}}
plz tell it agine to me by easy way.
thax
rafeek
Re: create trigger on database [message #257985 is a reply to message #257981] Thu, 09 August 2007 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rafeek wrote on Thu, 09 August 2007 20:26
i can not understant u bout
...
plz tell it agine to me by easy way.
thax

This is an english forum.
Please post in english.

Regards
Michel

Re: create trigger on database [message #258284 is a reply to message #257985] Fri, 10 August 2007 15:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd stick with AUDIT if. MarcL has nailed it I think. All this talk about Flashback has only confused the OP.

Rafeek, if you want to keep track of all changes in your data, you should seriously consider auditing the database.

MHE
Re: create trigger on database [message #258346 is a reply to message #258284] Sat, 11 August 2007 04:00 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
thax
i think i will create trigger on FLASHBACK_TRANSACTION_QUERY.
Re: create trigger on database [message #258349 is a reply to message #258346] Sat, 11 August 2007 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't create trigger on SYS objects.

Regards
Michel
Re: create trigger on database [message #258354 is a reply to message #258349] Sat, 11 August 2007 05:09 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
i will create view on FLASHBACK_TRANSACTION_QUERY.
['create view v_f as select * from FLASHBACK_TRANSACTION_QUERY ']
and create trigger on the v_f view
rafeek
Re: create trigger on database [message #258362 is a reply to message #258354] Sat, 11 August 2007 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And this will give what?
Nobody will use the view, so the trigger will never fire.

Regards
Michel
Re: create trigger on database [message #258366 is a reply to message #258362] Sat, 11 August 2007 05:58 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
why trigger will never fire.
Re: create trigger on database [message #258368 is a reply to message #258366] Sat, 11 August 2007 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because a trigger fires only if its condition is satisfied.
In your case, the condition will never be satified as NOBODY WILL USE THE VIEW.

Regards
Michel
Re: create trigger on database [message #258414 is a reply to message #258368] Sat, 11 August 2007 13:04 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
ok
thax for u.
how can i use flashback_transaction_query to call function in every time insert or update in any table ?
thax
Rafeek
Re: create trigger on database [message #258415 is a reply to message #258414] Sat, 11 August 2007 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Regards
Michel
Re: create trigger on database [message #258417 is a reply to message #258415] Sat, 11 August 2007 13:52 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
i will do

thax
Rafeek
Re: create trigger on database [message #258419 is a reply to message #258417] Sat, 11 August 2007 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You will do what?

Regards
Michel
Re: create trigger on database [message #258472 is a reply to message #258419] Sun, 12 August 2007 07:53 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
i use this view to create general trigger fire when insert or
update on any table on database
Re: create trigger on database [message #258474 is a reply to message #257512] Sun, 12 August 2007 08:42 Go to previous messageGo to next message
alokm
Messages: 5
Registered: February 2007
Junior Member
Suppose ur schema name is ABC then u can
use following trigger to prevent any insert or update on schema.
Instead of using before u can use after for audit purpose

CREATE OR REPLACE TRIGGER TRG_PREVENT_UPDATE before/after alter on AB.SCHEMA -- for each row
begin
if ORA_DICT_OBJ_OWNER ='ABC' and ora_sysevent = 'ALTER' AND ORA_DICT_OBJ_TYPE = 'TABLE'
then
raise_application_error (-20001, 'OPERATION NOT PERMITTED');
end IF;
END;
/

Hope this solves ur problem
Re: create trigger on database [message #258486 is a reply to message #258474] Sun, 12 August 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The question is:
Quote:
how can i create trigger on datebase fire when
insert or update on any table in databse .

your trigger is:
Quote:
CREATE OR REPLACE TRIGGER TRG_PREVENT_UPDATE before/after alter on AB.SCHEMA

Alter is neither insert nor update.

Regards
Michel
Re: create trigger on database [message #258571 is a reply to message #258486] Mon, 13 August 2007 01:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Once again: the OP wants to AUDIT. Why not use AUDIT? Messing with flashback to simulate built-in functionality. Very efficient. http://www.orafaq.com/forum/fa/917/0/

MHE
Re: create trigger on database [message #258585 is a reply to message #258571] Mon, 13 August 2007 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right Maarten but I feel that I have to show those who post about flashback they are wrong.

Regards
Michel
Re: create trigger on database [message #258605 is a reply to message #258585] Mon, 13 August 2007 03:10 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
what about performence of database if i use Flashback the time of Flashback in 100 min?
Re: create trigger on database [message #258607 is a reply to message #258605] Mon, 13 August 2007 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not relevant to this topic, continue on the topic you created for this question.

Regards
Michel
Re: create trigger on database [message #258610 is a reply to message #258607] Mon, 13 August 2007 03:30 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
it is relevant to this topic.
i want to known the the performance of database if i use flashback?
i will use flashback to get any update or insert in database
i will use flashback_transaction_query [view] to get any change in database .
i will use flashback in 100 min and create function run every
50 min this function get the new records in the flashback_transaction_query [view] (this records of tables i want to audit this tables is variable the use will select this tables )and insert it in table .

[Updated on: Mon, 13 August 2007 03:37]

Report message to a moderator

Re: create trigger on database [message #258611 is a reply to message #258610] Mon, 13 August 2007 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question is:
Quote:
how can i create trigger on datebase fire when insert or update on any table in databse .

How
Quote:
i want to known the the performance of database if i use flashback?

Can be relevant to this?
Moreover you created a topic for that, so don't pollute this one.

Regards
Michel

[Updated on: Mon, 13 August 2007 04:16]

Report message to a moderator

Re: create trigger on database [message #258643 is a reply to message #258611] Mon, 13 August 2007 04:08 Go to previous messageGo to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
i use flashback instead of trigger on datebase ;
i want to trigger on datebase to get all update and insert in any table i will get it fom flashback view
Re: create trigger on database [message #258645 is a reply to message #258643] Mon, 13 August 2007 04:15 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you won't get it from trigger on flashback view.

USE AUDIT.

Regards
Michel
Previous Topic: data filtering
Next Topic: locks
Goto Forum:
  


Current Time: Mon Dec 05 02:45:04 CST 2016

Total time taken to generate the page: 0.08837 seconds