Home » SQL & PL/SQL » SQL & PL/SQL » Need help building my first Oracle Trigger (Oracle, Windows XP)
icon14.gif  Need help building my first Oracle Trigger [message #337058] Tue, 29 July 2008 15:17 Go to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Hey ppl, This is my first time using Oracle and ive gotten used to the basics of creating Tables, Primary keys and such and using SQL Querys.

But now im stumped cause i need Help to create a trigger, The trigger will require three things in it. Due to the fact that this is my first time and ive only been told to make a trigger. but no one else has helped me out. im stumped. Ive had a look at examples and tuts. but they dont explain alot or if they do, Im confused at what there saying...so any chance someone could help me out in slow time to create this trigger?

Okay the three things i need it to do is:

1: Make sure the primary key cannot be deleted within a table and it will print a Msg saying otherwise.

2: When a primary key changes, my Project table will need to be updated automatically.

3: When a new entry is made into my Client table, i will need it to Insert that days date into a Field name. Most likely the Datejoined.

Now i have the format its ment to be like....
CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

So, im guessing it would be like...
CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
    ON Client
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code

EXCEPTION
    WHEN ...
    -- exception handling
END;

Dammit, I will need to edit this later. Going to work!

if you can understand it so far. please give me some help..in slow time Thx in advance!

[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Tue, 29 July 2008 23:00] by Moderator

Report message to a moderator

Re: Need help building my first Oracle Trigger [message #337072 is a reply to message #337058] Tue, 29 July 2008 19:47 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above


Re: Need help building my first Oracle Trigger [message #337109 is a reply to message #337058] Tue, 29 July 2008 23:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi and welcome to both the world of Oracle and to the forum!
We hope you will enjoy both; we at OraFAQ will be happy to help/guide you on your difficult path learning Oracle.
First of all, we do have some 'houserules' here; anacedent already pointed you to them. Please take the time to read them and try to comply; it will really help you getting answers more quickly (and more to the point).

Now for your questions:
Quote:
1: Make sure the primary key cannot be deleted within a table and it will print a Msg saying otherwise.

Not sure what you mean: do you mean that it should not be allowed to drop the primary key constraint, or that it should not be allowed to nullify (i.e. set to null, or empty) the column(s) that make up the primary key?
The latter is not allowed anyway (it comes for free with the PK); the first is not something you should be asked if you are just starting out.

Quote:
2: When a primary key changes, my Project table will need to be updated automatically.
3: When a new entry is made into my Client table, i will need it to Insert that days date into a Field name. Most likely the Datejoined.

These can be done very nicely with a trigger.
Remember that the [ and ] around [ FOR EACH ROW ] mean that that part is optional for the syntax, those are not really part of the code.

Now you give it a try first; try to create a trigger.
If you fail, come back and (very important!) show us what you tried and where you failed.
Remember, we do want to help you, but we will not do your homework for you!
Re: Need help building my first Oracle Trigger [message #337126 is a reply to message #337058] Wed, 30 July 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that a primary key value should NEVER change.

Regards
Michel
Re: Need help building my first Oracle Trigger [message #337131 is a reply to message #337126] Wed, 30 July 2008 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 30 July 2008 07:53
Note that a primary key value should NEVER change.

Regards
Michel


True, but it can (technically). I did not mention this, because it looks as though the instructor ignored this bit.
Re: Need help building my first Oracle Trigger [message #337349 is a reply to message #337131] Wed, 30 July 2008 11:24 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Instructor? Yeah....Just to let you know im learning this on my own. Im trying to learn the many different types of database systems. So that when it comes to developing a website, I will have experience on many platforms.

Im still at work, so when i get home i shall edit my Initial Post.

Thanks for letting me know that the PK shouldnt change. I Kinda already knew that, but nothing wrong being told it again.

As for your question Frank, about "Make sure the primary key cannot be deleted within a table and it will print a Msg saying otherwise."

What i mean, is at some points the Primary key might need to be changed, Edited. So i will require it to tell the User who is editing it, that the edit has been successful or that if the user trys to leave it blank (Null) or just try to delete it straight out. It will show a message to the user that, the action their trying to do isnt possible.

[Updated on: Wed, 30 July 2008 11:39]

Report message to a moderator

Re: Need help building my first Oracle Trigger [message #337361 is a reply to message #337349] Wed, 30 July 2008 12:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Shikaka wrote on Wed, 30 July 2008 18:24
Im trying to learn the many different types of database systems. So that when it comes to developing a website, I will have experience on many platforms.

Don't do that. Focus on a single database, whichever one you choose. If you are good at one, you will have less trouble getting the differences in others.
If you try to master "all" different databases, you will end up without understanding any of them truly. This will cause you not being able to understand what to use for which system.
It is the same as saying you are trying to learn C, C++, COBOL, Java, Ruby and Python at the same time, without much prior knowledge, so you can deal with any developer job you can get your hands on.

About the 'deleting the PK': The primary key is a constraint. This constraint consists of one to several columns. Deleting the PK means that you remove the constraint.
What you are trying to say is to nullify the PK-column(s).
As I said before: that is not allowed by Oracle; no need to write a trigger for that.
Re: Need help building my first Oracle Trigger [message #337387 is a reply to message #337361] Wed, 30 July 2008 13:27 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Frank wrote on Wed, 30 July 2008 12:02
Shikaka wrote on Wed, 30 July 2008 18:24
Im trying to learn the many different types of database systems. So that when it comes to developing a website, I will have experience on many platforms.

Don't do that. Focus on a single database, whichever one you choose. If you are good at one, you will have less trouble getting the differences in others.
If you try to master "all" different databases, you will end up without understanding any of them truly. This will cause you not being able to understand what to use for which system.
It is the same as saying you are trying to learn C, C++, COBOL, Java, Ruby and Python at the same time, without much prior knowledge, so you can deal with any developer job you can get your hands on.



That maybe true but it still good to have hands on experience wither or not. Like, how a chiropractor needs to learn all of the basics from a medical course wither or not it is required. This way if he gets a patient that has a problem which isnt actually needing a chiropractor. he can atleast give proper advice on where to find treatment.


By doing this, i will learn the limitations within ORACLE and such and hands on experience. which help me give advice, or atleast know which direction to go and because im not learning all the database systems at once. it doesnt show harm.


Right anyway. this is going WAY! off topic lol.

Right now that im home. im going to give this a try.
Re: Need help building my first Oracle Trigger [message #337391 is a reply to message #337387] Wed, 30 July 2008 14:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Shikaka wrote on Wed, 30 July 2008 20:27


That maybe true but it still good to have hands on experience wither or not. Like, how a chiropractor needs to learn all of the basics from a medical course wither or not it is required. This way if he gets a patient that has a problem which isnt actually needing a chiropractor. he can atleast give proper advice on where to find treatment.


By doing this, i will learn the limitations within ORACLE and such and hands on experience. which help me give advice, or atleast know which direction to go and because im not learning all the database systems at once. it doesnt show harm.


Right anyway. this is going WAY! off topic lol.

Right now that im home. im going to give this a try.

You will NOT learn anything specific to Oracle; not its limitations and not its strengths, unless you take the time to specialise in it.
It will take about a year to two years of project experience before one really gets a grip on the "Oracle/SQLServer/MySQL/etc"-specific things.
If you refuse to specialise, you will either hold things you don't know for short-comings of a certain RDBMS, or you will trust each urban legend you hear.

I don't trust chiropracticioners either, by the way.
Re: Need help building my first Oracle Trigger [message #337890 is a reply to message #337391] Fri, 01 August 2008 08:25 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Frank wrote on Wed, 30 July 2008 14:07
Shikaka wrote on Wed, 30 July 2008 20:27


That maybe true but it still good to have hands on experience wither or not. Like, how a chiropractor needs to learn all of the basics from a medical course wither or not it is required. This way if he gets a patient that has a problem which isnt actually needing a chiropractor. he can atleast give proper advice on where to find treatment.


By doing this, i will learn the limitations within ORACLE and such and hands on experience. which help me give advice, or atleast know which direction to go and because im not learning all the database systems at once. it doesnt show harm.


Right anyway. this is going WAY! off topic lol.

Right now that im home. im going to give this a try.


You will NOT learn anything specific to Oracle; not its limitations and not its strengths, unless you take the time to specialise in it.
It will take about a year to two years of project experience before one really gets a grip on the "Oracle/SQLServer/MySQL/etc"-specific things.
If you refuse to specialise, you will either hold things you don't know for short-comings of a certain RDBMS, or you will trust each urban legend you hear.

I don't trust chiropracticioners either, by the way.



Well i have plenty of time to learn it. Your making it sound as if im stuck with a time frame.

Im ready to learn. Oh and if you dont trust chiropracticioners, you need to open your mind more. The ones that I have went to have taken their jobs with passion. Compared to the MD's I have visited, who sometimes dont even relise what their doing and end up giving out the wrong pill's or something similiar. Did you relise that doctors guess!? Dont get me wrong, Chiro's can only fix a limited amount compared to MD's. But when i have something wrong with my neck. i'd rather go to a chiro and receive their treatment than injections to loosen up my neck muscle.

Any way back on topic.

Ive had a go and my Inexperience has gotten me this lump of code which "Mutates". Am i going in the wrong direction?


create or replace trigger update_client
after insert on my_client
for each row
begin
if inserting then
   insert into My_Client 
   values (:new.ClientID, :new.CName, :new.AddrID, current_date);
 	   --elsif updating then
   --set My_Client values (:new.ClientID, :new.CName, :new.AddrID, current_date);
 	  -- elsif deleting then
		--	RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
 end if;
 end;
Re: Need help building my first Oracle Trigger [message #337896 is a reply to message #337890] Fri, 01 August 2008 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to use insert in an insert.
If you want to set the values just put them in NEW record.
NEW record is the record you are currently inserting.
Trying to use insert into insert trigger is trying to insert a new (second) row at the same time than the first one.

Regards
Michel
Re: Need help building my first Oracle Trigger [message #338571 is a reply to message #337896] Tue, 05 August 2008 06:41 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Im confused where would i put that Line?

create or replace trigger update_client
after insert on my_client
for each row
begin
if New Record then
   insert into My_Client 
   values (:new.ClientID, :new.CName, :new.AddrID, current_date);
 	   --elsif updating then
   --set My_Client values (:new.ClientID, :new.CName, :new.AddrID, current_date);
 	  -- elsif deleting then
		--	RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
 end if;
 end;

[Updated on: Tue, 05 August 2008 06:41]

Report message to a moderator

Re: Need help building my first Oracle Trigger [message #338594 is a reply to message #338571] Tue, 05 August 2008 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Im confused where would i put that Line?

Which line?

Regards
Michel
Re: Need help building my first Oracle Trigger [message #338607 is a reply to message #338594] Tue, 05 August 2008 07:37 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Michel Cadot wrote on Tue, 05 August 2008 07:22
Quote:
Im confused where would i put that Line?

Which line?

Regards
Michel



THe new Record,.
Re: Need help building my first Oracle Trigger [message #338616 is a reply to message #338607] Tue, 05 August 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does mean "if New Record then"?

Regards
Michel
Re: Need help building my first Oracle Trigger [message #338641 is a reply to message #337058] Tue, 05 August 2008 09:20 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Okay, had a few words with someone I know and he gave me this...but for some reason it doesnt work on a test table i made and yet he says it works fine with his.


what could be wrong...

create or replace trigger update_client
after insert or update or delete of ClientID on my_test
for each row
begin
if inserting then
	insert into My_test
		values (:new.ClientID, :new.Name, new:sysdate);
 	-- elsif updating then
  	-- insert into My_test values (:new.ClientID, :new.Name,  :old.DateJoined);
 	   elsif deleting then
			RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
end if;
end;


and it keeps giving me a Table mutating error.
Re: Need help building my first Oracle Trigger [message #338651 is a reply to message #338641] Tue, 05 August 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 01 August 2008 15:38
Why do you want to use insert in an insert.
If you want to set the values just put them in NEW record.
NEW record is the record you are currently inserting.
Trying to use insert into insert trigger is trying to insert a new (second) row at the same time than the first one.

Regards
Michel


Re: Need help building my first Oracle Trigger [message #338679 is a reply to message #338651] Tue, 05 August 2008 11:18 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Michel Cadot wrote on Tue, 05 August 2008 10:13
Michel Cadot wrote on Fri, 01 August 2008 15:38
Why do you want to use insert in an insert.
If you want to set the values just put them in NEW record.
NEW record is the record you are currently inserting.
Trying to use insert into insert trigger is trying to insert a new (second) row at the same time than the first one.

Regards
Michel






So then i would having something like this no?

create or replace trigger update_client
after insert or update or delete of ClientID on my_test
for each row
begin
if inserting then
	New Record into My_test
		values (:new.ClientID, :new.Name, new:sysdate);
 	-- elsif updating then
  	-- insert into My_test values (:new.ClientID, :new.Name,  :old.DateJoined);
 	   elsif deleting then
			RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
end if;
end;
Re: Need help building my first Oracle Trigger [message #338684 is a reply to message #338679] Tue, 05 August 2008 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I give up.
You can pass many days, months and years without finding the correct using trials and errors method.
Just spend a couple of hours reading the documentation.

Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Regards
Michel
Re: Need help building my first Oracle Trigger [message #338696 is a reply to message #338641] Tue, 05 August 2008 12:34 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Shikaka wrote on Tue, 05 August 2008 10:20
Okay, had a few words with someone I know and he gave me this...but for some reason it doesnt work on a test table i made and yet he says it works fine with his.



He's wrong. Obviously it's not the same code or logic.
Re: Need help building my first Oracle Trigger [message #338700 is a reply to message #338696] Tue, 05 August 2008 13:32 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Uh huh, i clearly noticed that after i got an error.

Anything else, to help me with this problem?
Re: Need help building my first Oracle Trigger [message #338701 is a reply to message #337058] Tue, 05 August 2008 13:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> it will print a Msg saying otherwise.
How does a trigger which runs deep inside the database server communicate with the end user?

>Anything else, to help me with this problem?
& exactly what is "this problem"?
Where is the DDL for your tables?
Where is the DML for the test data?
Where is the expected/ desired results enumerated?

Since you don't follow previously mentioned Posting Guidelines, You're on Your Own (YOYO)!

[Updated on: Tue, 05 August 2008 14:17] by Moderator

Report message to a moderator

Re: Need help building my first Oracle Trigger [message #338784 is a reply to message #338679] Tue, 05 August 2008 23:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Shikaka wrote on Tue, 05 August 2008 18:18

So then i would having something like this no?

create or replace trigger update_client
after insert or update or delete of ClientID on my_test
for each row
begin
if inserting then
	New Record into My_test
		values (:new.ClientID, :new.Name, new:sysdate);
 	-- elsif updating then
  	-- insert into My_test values (:new.ClientID, :new.Name,  :old.DateJoined);
 	   elsif deleting then
			RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
end if;
end;


Let me try to rephrase what Michel has been trying to tell you since the start of this thread.
Where did you get the idea that having a trigger actually STOPS the database from doing the original insert?
You insert a record into your database. This fires up the trigger, in which you try to insert the same record. Guess what happens...
Get rid of the insert; the database is busy inserting a record
If you want control over values of columns, simply assign a new value to :new.<your_column> and leave the trigger. The database will then continue the insert with the altered values.
Re: Need help building my first Oracle Trigger [message #338975 is a reply to message #338784] Wed, 06 August 2008 06:26 Go to previous messageGo to next message
Shikaka
Messages: 10
Registered: July 2008
Junior Member
Frank wrote on Tue, 05 August 2008 23:12
Shikaka wrote on Tue, 05 August 2008 18:18

So then i would having something like this no?

create or replace trigger update_client
after insert or update or delete of ClientID on my_test
for each row
begin
if inserting then
	New Record into My_test
		values (:new.ClientID, :new.Name, new:sysdate);
 	-- elsif updating then
  	-- insert into My_test values (:new.ClientID, :new.Name,  :old.DateJoined);
 	   elsif deleting then
			RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
end if;
end;


Let me try to rephrase what Michel has been trying to tell you since the start of this thread.
Where did you get the idea that having a trigger actually STOPS the database from doing the original insert?
You insert a record into your database. This fires up the trigger, in which you try to insert the same record. Guess what happens...
Get rid of the insert; the database is busy inserting a record
If you want control over values of columns, simply assign a new value to :new.<your_column> and leave the trigger. The database will then continue the insert with the altered values.



Hey, Thanks for the reply. After that YO YO post. i was expecting no help ^^

So ive got rid of the Insert and put an Update.

is this along the lines? If no please edit my code and put appropriate. cause im stressing lol. dont have a clue lol.

create or replace trigger update_client
after insert or update or delete of ClientID on my_test
for each row
begin
if inserting then
	 UPDATE my_test SET datejoined = sysdate;
 	--elsif updating then
  		--insert into My_test values (:new.ClientID, :new.Name,  :old.DateJoined);
 	  -- elsif deleting then
			--RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
end if;
end;
Re: Need help building my first Oracle Trigger [message #338981 is a reply to message #338975] Wed, 06 August 2008 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 05 August 2008 18:27
I give up.
You can pass many days, months and years without finding the correct using trials and errors method.
Just spend a couple of hours reading the documentation.

Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Regards
Michel


Re: Need help building my first Oracle Trigger [message #339058 is a reply to message #338975] Wed, 06 August 2008 12:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Shikaka wrote on Wed, 06 August 2008 13:26
Frank wrote on Tue, 05 August 2008 23:12

Get rid of the insert; the database is busy inserting a record
If you want control over values of columns, simply assign a new value to :new.<your_column> and leave the trigger. The database will then continue the insert with the altered values.


So ive got rid of the Insert and put an Update.



Reread my reply. Where does it say remove the insert and add an update?.
Re: Need help building my first Oracle Trigger [message #339114 is a reply to message #338975] Wed, 06 August 2008 15:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Don't insert within the trigger. Don't use an update statement within the trigger. To modify values in the existing row being processed that caused the trigger to fire, assign the values using:

:new.column_name := new_value;

substituting the actual name of the column and new value that you want to assign to it. Look at some of the examples in the chapter on triggers to see how this is done:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

The only time that you should use an insert or update is when you are inserting or updating another table, such as an audit table. If you try to modify the table that fired the trigger by inserting or updating, you will likely get some sort of mutating error.

Also, you should probably be using a before insert trigger rather than an after insert trigger.

[Updated on: Wed, 06 August 2008 23:09]

Report message to a moderator

Re: Need help building my first Oracle Trigger [message #339151 is a reply to message #338975] Wed, 06 August 2008 22:03 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Dear Shikaka,

Perhaps, You are so much confused.
Appriciate your spirit. You haven't give up yet.. Smile

First figure out, why actually you want tigger on the table. In other words, first define what that trigger will actually do. Then read some basics about how oracle triggers works. Then i think, you will be quite clear.

About your last posted code..
create or replace trigger update_client
after insert or update or delete of ClientID on my_test
for each row
begin
if inserting then
	 UPDATE my_test SET datejoined = sysdate;
 	--elsif updating then
  		--insert into My_test values (:new.ClientID, :new.Name,  :old.DateJoined);
 	  -- elsif deleting then
			--RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
end if;
end;


Make chages like

create or replace trigger update_client
after insert or update or delete of ClientID on my_test
for each row
begin
if inserting then
	 :new.datejoined = sysdate;
 	--elsif updating then
  		--insert into My_test values (:new.ClientID, :new.Name,  :old.DateJoined);
 	  -- elsif deleting then
			--RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
end if;
end;


That's all.. Smile

Perhaps, you were thinking that , the is only way to change table data is to use insert or update or delete statement. That is partially true.
Within trigger if you want to change the data for which the trigger has been fired, then , you should not use insert/update/delete statements.
In that case you just need to use
:new.columnname := value
(as told by barbara).

Hope your trigger gets build ....

Regards,
Dipali..

[Updated on: Wed, 06 August 2008 22:04]

Report message to a moderator

Re: Need help building my first Oracle Trigger [message #339156 is a reply to message #339151] Wed, 06 August 2008 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One more that is unable to eat by himself and will always need to be spoonfed.
Link to documentation was already given to OP twice, he just did not and want not or cannot read it.
Desesperate!

Regards
Michel

[Edit: typos]

[Updated on: Thu, 07 August 2008 00:35]

Report message to a moderator

Re: Need help building my first Oracle Trigger [message #339157 is a reply to message #337058] Wed, 06 August 2008 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Hope your trigger gets build ....
Without external assistance, my pet pig has a better chance to fly.

Re: Need help building my first Oracle Trigger [message #339162 is a reply to message #339157] Wed, 06 August 2008 23:37 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Obviously anacedent,

We can just give him guidance or suggestions.. Ultimately, he has to give time to read and create it..

Regards,
Dipali...
Re: Need help building my first Oracle Trigger [message #339182 is a reply to message #339162] Thu, 07 August 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
he has to give time to read

Obviously he can't.

Regards
Michel
Re: Need help building my first Oracle Trigger [message #339230 is a reply to message #339151] Thu, 07 August 2008 02:14 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
@Dipali

Good that you brought this topic to an end.

Contrary to what many think, you did not spoonfeed: you provided the knowledge & information in a simplified way.

Hope Shikaka stays put !!!
Re: Need help building my first Oracle Trigger [message #339245 is a reply to message #339230] Thu, 07 August 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If ":new.datejoined = sysdate;" is not spoonfeeding what is spoonfeeding?

Regards
Michel
Re: Need help building my first Oracle Trigger [message #339260 is a reply to message #339245] Thu, 07 August 2008 03:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Don't worry the ":new.datejoined = sysdate;" on the spoon will be spit out again when it runs on the

ORA-06550: line 4, column 9:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
   := . ( @ % ;

Re: Need help building my first Oracle Trigger [message #339272 is a reply to message #339260] Thu, 07 August 2008 03:40 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

ohh...

@Shikaka, ":" was missing in that code, in my post to you..
Corrected one is:

create or replace trigger update_client
after insert or update or delete of ClientID on my_test
for each row
begin
if inserting then
	 :new.datejoined = sysdate;
 	--elsif updating then
  		--insert into My_test values (:new.ClientID, :new.Name,  :old.DateJoined);
 	  -- elsif deleting then
			--RAISE_APPLICATION_ERROR (-20000, 'You Cannot Delete a Primary Key Entry.');
end if;
end;



@Charry, Michel and Thomas: Thanks.

Regards,
Dipali..
Previous Topic: Display results from Oracle Stored Procedure
Next Topic: Can anybody solved this query
Goto Forum:
  


Current Time: Sun Dec 04 18:29:45 CST 2016

Total time taken to generate the page: 0.13275 seconds