Home » SQL & PL/SQL » SQL & PL/SQL » creating and running a trigger
creating and running a trigger [message #337782] Fri, 01 August 2008 03:49 Go to next message
s_ali_hassan
Messages: 18
Registered: August 2008
Junior Member
Hi,

I am fairly new to oracle and SQL, and i am currently trying to create and run a trigger. I have been on various websites etc and have been checking on how to go about creating one.
I'm not sure on how to run it or if my logic is correct.

What i am trying to do is after a new row has been added to Table A, one of the columns is updated using the data from another column in the same table.

Table A has 4 columns. A, B, C and D. B, C and D have values in them, but A is blank. When the table is updated with a new row (with values blank, 1, 2, 3) the trigger should copy the value from B (which is 1) and copy it to column A, so the values in the newly inserted row would now be 1, 1, 2, 3. It should only do this for rows whic have a null value for column A.

I have had a try at it below

CREATE TRIGGER trig1
AFTER INSERT ON TableA
FOR EACH ROW
WHEN (TableA.A is null)
BEGIN
UPDATE TableA Set TableA.A=TableA.B
END trig1;
run;


Not sure if this works as i have had a try, and am not 100% sure on how to run it.

Any help will be greatly appreciated.
Thanks
Re: creating and running a trigger [message #337783 is a reply to message #337782] Fri, 01 August 2008 03:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A much better way to do this would be as a BEFORE UPDATE trigger:
CREATE TRIGGER trig1
BEFORE INSERT ON TableA
FOR EACH ROW
WHEN (TableA.A is null)
BEGIN
  :new.A := :new.B;
END trig1;
/
Re: creating and running a trigger [message #337811 is a reply to message #337782] Fri, 01 August 2008 04:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As an addition to JRowbottom's advise, I will add a short explanation to show why your solution is wrong.
First of all, you have no where-clause in your update-statement, so the update will alter each and every record in your table.
Secondly: you cannot fire DML-statements in a row trigger ("FOR EACH ROW") that manipulate the triggering table. This is the infamous mutating table problem (Google for that; zillions of hits).
Finally, JRowbottom's solution is 'cheaper' then yours, since his changes the value of the field prior to updating, so there is only one insert needed. Should your solution work, you'd insert the record and do an update on the inserted record after that. Waste of resources.
Re: creating and running a trigger [message #337838 is a reply to message #337782] Fri, 01 August 2008 05:39 Go to previous messageGo to next message
s_ali_hassan
Messages: 18
Registered: August 2008
Junior Member
Thanks for the replies.

I have tried that piece of coed but i get the same error.
Ora-04076: invalid NEW or OLD specification on line 4.

Not sure how to solve this, but is it wanting me to specify OLD ot NEW on the WHEN clause?

I will try the following and see if it works.

....
when (new.TableA.A is null)
...


If you have any suggestions please let me know.
Re: creating and running a trigger [message #337844 is a reply to message #337782] Fri, 01 August 2008 05:59 Go to previous messageGo to next message
s_ali_hassan
Messages: 18
Registered: August 2008
Junior Member
that seemed to work, however i get an error message.

warning: trigger created with compilation errors

Does this mean that the trigger will still run?

so if there is an insert on tableA, the trigger will automatically fire?
Re: creating and running a trigger [message #337849 is a reply to message #337844] Fri, 01 August 2008 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I will try the following and see if it works.

Is there the table name in the body when you use NEW?

Quote:
warning: trigger created with compilation errors

show error trigger ...

Quote:
Does this mean that the trigger will still run?

No. How could it run if it can't compile?

Regards
Michel

[Edit: Add missing words]

[Updated on: Fri, 01 August 2008 08:29]

Report message to a moderator

Re: creating and running a trigger [message #337875 is a reply to message #337782] Fri, 01 August 2008 07:49 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
You almost had it and you did try, so do the following

when (new.A is null)

Re: creating and running a trigger [message #337899 is a reply to message #337782] Fri, 01 August 2008 08:53 Go to previous messageGo to next message
s_ali_hassan
Messages: 18
Registered: August 2008
Junior Member
have been working on it, but unfortunately its not working.
The table that im trying to update is actually quite a big table with a number of columns and has foreign keys to other tables in the schema. I think the update via the trigger may not be working because it could be breaking referential integrity? not sure.

i'm now trying to get the trigger to update with predetermined values, although this is also not compiling.

CREATE TRIGGER trig1
AFTER INSERT ON servername.TableA
FOR EACH ROW
WHEN (new.A is null)
BEGIN
:new.A='Bla'
:new.B='Bla Bla'
:new.C='{EE263562-3BB0-4AFB-8A46-BC796125C962}'
:new.D='Customer Services 1A'
END trig1;


anyone know what could be stopping it from compiling correctly?
Re: creating and running a trigger [message #337900 is a reply to message #337782] Fri, 01 August 2008 09:03 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

>have been working on it, but unfortunately its not working.
>although this is also not compiling.
>anyone know what could be stopping it from compiling correctly?

My car is not working.
Please tell me how to make my car go.

You might get better response by reading & FOLLOWING the Posting Guidelines
which is use of COPY & PASTE so we can see the actual error!
Re: creating and running a trigger [message #337901 is a reply to message #337782] Fri, 01 August 2008 09:15 Go to previous messageGo to next message
s_ali_hassan
Messages: 18
Registered: August 2008
Junior Member
error as below:

SQL> CREATE TRIGGER trig1
  2  AFTER INSERT ON servername.TableA
  3  FOR EACH ROW
  4  WHEN (new.A is null)
  5  BEGIN
  6  :new.A='Bla'
  7  :new.B='Bla Bla'
  8  :new.C='{EE263562-3BB0-4AFB-8A46-BC796125C962}'
  9  :new.D='Customer Services 1A'
 10  END trig1;
 11  /

Warning: Trigger created with compilation errors.

SQL>


thats everything copied and pasted.

Re: creating and running a trigger [message #337902 is a reply to message #337782] Fri, 01 August 2008 09:17 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
When you get the message "Warning: Trigger created with compilation errors." then simply type in

show errors


and all the errors it found will be displayed
Re: creating and running a trigger [message #337903 is a reply to message #337782] Fri, 01 August 2008 09:23 Go to previous messageGo to next message
s_ali_hassan
Messages: 18
Registered: August 2008
Junior Member
thanks Bill.

error:
Errors for TRIGGER TRIG1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/13     PLS-00103: Encountered the symbol "=" when expecting one of the
         following:
         := . ( @ % ; indicator
Re: creating and running a trigger [message #337904 is a reply to message #337782] Fri, 01 August 2008 09:25 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
The error is exactly what it says. You perfrom an assignment with := not = and end the statement with a semi colon.


junk := 0;
Re: creating and running a trigger [message #337908 is a reply to message #337782] Fri, 01 August 2008 09:35 Go to previous messageGo to next message
s_ali_hassan
Messages: 18
Registered: August 2008
Junior Member
SQL> CREATE TRIGGER trig1
  2  AFTER INSERT ON firstwave.ngbedsactivity
  3  FOR EACH ROW
  4  WHEN (new.agentid is null)
  5  BEGIN
  6  :new.agentid:="agent";
  7  :new.agentname:="agentname";
  8  :new.teamid:="1234";
  9  :new.teamname:="Customer Services 1A";
 10  END trig1;
 11  /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER TRIG1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PL/SQL: Statement ignored
2/15     PLS-00201: identifier 'agent' must be declared
3/1      PL/SQL: Statement ignored
3/17     PLS-00201: identifier 'agentname' must be declared
4/1      PL/SQL: Statement ignored
4/14     PLS-00201: identifier '1234' must be declared
5/1      PL/SQL: Statement ignored
5/16     PLS-00201: identifier 'Customer Services 1A' must be declared

what does it mean by declared? do i have to declare it as a variable?

I think i need to do some in depth reading......then come back to it.

thanks for your help guys.
Re: creating and running a trigger [message #337910 is a reply to message #337782] Fri, 01 August 2008 09:37 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
You are SO close. Literals in oracle are surrounded by single quotes, not double.

junk := 'apple';
Re: creating and running a trigger [message #337911 is a reply to message #337782] Fri, 01 August 2008 09:42 Go to previous messageGo to next message
s_ali_hassan
Messages: 18
Registered: August 2008
Junior Member
finally!

its working.

SQL> CREATE TRIGGER trig1
  2  BEFORE INSERT ON firstwave.ngbedsactivity
  3  FOR EACH ROW
  4  WHEN (new.agentid is null)
  5  BEGIN
  6  :new.agentid:='agent';
  7  :new.agentname:='agentname';
  8  :new.teamid:='12';
  9  :new.teamname:='Customer Services 1A';
 10  END trig1;
 11  /

Trigger created.


sorted
Thanks alot.
Re: creating and running a trigger [message #337916 is a reply to message #337911] Fri, 01 August 2008 10:03 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A useful link: PL/SQL User's Guide and Reference

Regards
Michel
Previous Topic: table confusion
Next Topic: Case Statement not working in Insert statement
Goto Forum:
  


Current Time: Sun Dec 04 10:30:45 CST 2016

Total time taken to generate the page: 0.13946 seconds