Compiling Triggers in sql*plus

[ro88o]

Hi,

I'm trying to create and compile some triggers in sql*plus but I think I've been given incorrect information on how to compile them. I type EDIT insert_derive_duration and an editor came up which I used to create the following:

CREATE OR REPLACE TRIGGER insert_derive_duration
AFTER INSERT
OF date_to, date_from
ON contractinfo
FOR EACH ROW
DECLARE
totalDuration NUMBER := 0;
BEGIN
  totalDuration := date_to - date_from;
  DBMS-OUTPUT.PUTLINE(totalDuration);
END;

Now when I have saved (as insert_derive_duration.sql) I'm currently doing @insert_derive_duration and expected to receive the message Trigger created but instead it does nothing. Does anyone know how I go about compiling and running my trigger please ?

Thanks in advance.

 

[lewisp]

Include a slash (/) at the end of your script, followed by blank line.

 

[ro88o]

It didn't work

Any other ideas ?

 

[lewisp]

I've spotted you have an error in your script.

 DBMS[COLOR=red]_[/color]OUTPUT.PUT[COLOR=red]_[/color]LINE(totalDuration);

If you type L after you have run the script, what is in the SQL buffer? Also type SHOW ERROR to see what errors occurred when you compiled.

 

[ro88o]

If you type L after you have run the script, what is in the SQL buffer? Also type SHOW ERROR to see what errors occurred when you compiled.

I can't even get that far When I type @[trigger name] to compile it doesn't actually do anything, I can carry on typing and/or pressing return and I have to do Ctrl+C and then return to get back to the SQL> prompt.

 

[ChrisHunt]

I think, in SQL*Plus, you need to have an otherwise blank line at the end of your script with a / in it. Like this:

CREATE OR REPLACE TRIGGER insert_derive_duration
AFTER INSERT
OF date_to, date_from
ON contractinfo
FOR EACH ROW
DECLARE
totalDuration NUMBER := 0;
BEGIN
  totalDuration := date_to - date_from;
  DBMS_OUTPUT.PUT_LINE(totalDuration);
END;
[red]/[/red]

It tells SQL*Plus to come out of entry mode and execute whatever you've been typing (or getting from a file).

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd