Problem with a trigger,help! [message #36649] |
Mon, 10 December 2001 05:14 |
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 |
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 |
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 |
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!
----------------------------------------------------------------------
|
|
|