Home » SQL & PL/SQL » SQL & PL/SQL » Problem with a trigger,help!
Problem with a trigger,help! [message #36649] Mon, 10 December 2001 05:14 Go to next message
Eva
Messages: 16
Registered: July 2001
Junior Member
I am new to working with triggers,so I have the following problem:
I have a table that has information about the spots that a tv channel broadcasts.

create table spot
(
spot_name varchar2(15) NOT NULL,
spot_id number(5),
company varchar2(20) NOT NULL,
upd_ts DATE default sysdate,
upd_by varchar(15) default (USER),
CONSTRAINT spot_pk PRIMARY KEY (spot_id)
);

I want to make a trigger that after each update/insertion/dletion will print a message that will inform of the number of spots currently in database.

I create the trigger

CREATE TRIGGER print
BEFORE DELETE OR INSERT OR UPDATE ON spot
FOR EACH ROW

DECLARE
cnt number;
result number;
before_res number;
after_res number;

BEGIN
SELECT COUNT(spot_id) INTO cnt FROM spot;

IF cnt > 0 THEN
select SUM(old.spot_id) INTO before_res from spot;
select SUM(new.spot_id) INTO after_res from spot;
result := after_res - before_res ;
DBMS_OUTPUT.enable;
DBMS_OUTPUT.PUT('Result' || result);
DBMS_OUTPUT.new_line;
END IF;
END;
/

I get the following errors :
15/1 PL/SQL: SQL Statement ignored
15/16 PL/SQL: ORA-00904: invalid column name
17/1 PL/SQL: SQL Statement ignored
17/16 PL/SQL: ORA-00904: invalid column name

I cannot solve them.Does anyone has any suggestion?
The help will be valuable!
Thank you a lot!
Eva

----------------------------------------------------------------------
Re: Problem with a trigger,help! [message #36650 is a reply to message #36649] Mon, 10 December 2001 05:42 Go to previous messageGo to next message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Try:

select SUM(:old.spot_id) INTO before_res from spot;
select SUM(:new.spot_id) INTO after_res from spot;

Though the logic is flawed anyway, since the trigger will probably mutate... I could be wrong here, but I was under the impression you can't select from the table on which the trigger is set?

And no, you won't be able to call a procedure to do it either...

What you are trying to do is cited as an example of the impossible...

http://otn.oracle.com/doc/server.804/a58241/ch9.htm#2634

----------------------------------------------------------------------
Re: Problem with a trigger,help! [message #36663 is a reply to message #36649] Mon, 10 December 2001 22:53 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
Hi
First if you want to count the number of records in your table you don't have to make sum(spot_id) because this will not return you the number of records.
A better ideea it's to use two triggers, before and after, and do not use the statement for each row because trigger will mutating.
I hope this will help you.
Here is the triggers:

CREATE OR REPLACE TRIGGER print_before
BEFORE DELETE OR INSERT OR UPDATE ON SPOT
DECLARE
cnt NUMBER;
BEGIN
SELECT COUNT(spot_id) INTO cnt FROM SPOT;
dbms_output.put_line('Number of spots in the database before operation: ' || TO_CHAR(cnt));
END;

CREATE OR REPLACE TRIGGER print_after
AFTER DELETE OR INSERT OR UPDATE ON SPOT
DECLARE
cnt NUMBER;

BEGIN
SELECT COUNT(spot_id) INTO cnt FROM SPOT;
IF UPDATING THEN
dbms_output.put_line('Updating table!');
dbms_output.put_line('Number of spots in the database after updating: ' || TO_CHAR(cnt));
END IF;

IF DELETING THEN
dbms_output.put_line('Deleting!');
dbms_output.put_line('Number of spots in the database after deleting: ' || TO_CHAR(cnt));
END IF;

IF INSERTING THEN
dbms_output.put_line('Inserting!');
dbms_output.put_line('Number of spots in the database after inserting: ' || TO_CHAR(cnt));
END IF;
END;

Bye

----------------------------------------------------------------------
Re: Problem with a trigger,help! [message #36665 is a reply to message #36649] Tue, 11 December 2001 01:20 Go to previous message
Eva
Messages: 16
Registered: July 2001
Junior Member
I have done what you suggested me (the 2 triggers option, print_before and print_after) but it doesn't print anything!
For example after the update on spot, the actions are performed but the dbms_output.put_line('Number of spots in the database after updating: ' || TO_CHAR(cnt));
does not produce anything!
I would appreciate and that help!
Thank you!

----------------------------------------------------------------------
Previous Topic: HELP! Compatibility problem?
Next Topic: Re: Over 4 ways to send mail, and UTL_SMTP sample attached FYI
Goto Forum:
  


Current Time: Fri Aug 14 15:53:30 CDT 2020