CODEWITHSUNDEEP
sqloracle-apextriggers
user3394087
user3394087

Reputation:

SQL insert trigger before insert not working

I got two tables:

log:

Column Name Data Type   Nullable    Default Primary Key
ID_LOG  NUMBER  No  -   -
EVENT   VARCHAR2(255)   Yes -   -
TIMESTAMP_LOG   TIMESTAMP(6)    Yes -   -

task:

Column Name Data Type   Nullable    Default Primary Key
ID  NUMBER(10,0)    No  -   1
PRIORITY_ID NUMBER(10,0)    No  -   -
TITLE   VARCHAR2(50)    No  -   -
TASK_DESCRIPTION    VARCHAR2(2048)  No  -   -
EXPECTED_CONSUM_TIME    NUMBER(10,0)    Yes -   -
TIME_UNIT_ID    NUMBER(10,0)    Yes -   -
DEADLINE    DATE    Yes -   -
CONTRACTOR_ID   NUMBER(10,0)    No  -   -
IMPLEMENTER_ID  NUMBER(10,0)    No  -   -
TYPE_ID NUMBER(10,0)    No  -   -

I created a trigger, so it should insert log with current timestamp when I create task.

CREATE OR REPLACE TRIGGER TR_TASK_LOGGING
BEFORE INSERT ON task
FOR EACH ROW
BEGIN  
    INSERT INTO log (id_log, event, timestamp_log)
    VALUES (LOG_SEQ.nextval, 'New task created: ' + :new.title, CURRENT_TIMESTAMP);
END TR_TASK_LOGGING;

The creation of that trigger is without any errors.

However, if I try to insert:

INSERT INTO task (ID, PRIORITY_ID, title, task_description, EXPECTED_CONSUM_TIME, TIME_UNIT_ID, DEADLINE, CONTRACTOR_ID, IMPLEMENTER_ID, TYPE_ID) VALUES (TASK_SEQ.nextval, 3, 'something', 'something', 8, 2, '30-05-2014', 4, 8, 22);

I got these errors:

ORA-01722: invalid number
ORA-06512: on "TEAM_6.TR_TASK_LOGGING", line 2
ORA-04088: error during executing trigger 'TEAM_6.TR_TASK_LOGGING'

If there is no trigger created the creation is working. Any ideas, where could be the problem?

Upvotes: 0

Views: 227

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

The problem is this bit

'New task created: ' + :new.title

I'm assuming that your goal is to concatenate the :new.title to the string 'New task created: '. The string concatentation operator in PL/SQL is not +, however, it is ||. + only works to add two numbers. Oracle tries to convert both 'New task created: ' and :new.title to a number to add them-- that obviously fails, hence the error. You can use

'New task created: ' || :new.title

You could also use the concat function.

Upvotes: 1

Related Questions