CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-insertdatabase-trigger
TheBiz
TheBiz

Reputation: 83

ORA-00904: "TYPE": invalid identifier on trigger. Where have I gone wrong?

I have created a table where the primary key auto increments. I have sent up the relevant data, sequences and triggers. But I have had no hope in executing an automated increment as of yet and my trigger keeps throwing errors.

Code:

CREATE SEQUENCE empid_seq
INCREMENT BY 1
START WITH 1101
NOCACHE;

CREATE TRIGGER empuser_trigger
before insert on empuser
for each row
begin
if :new.empuserid.nextval into :new.empuserid from dual;
end if;
end;
/


INSERT INTO empuser VALUES(empid_seq.nextval, 'James', 'Kirk', '+447911123456', '401', '203', (SELECT messageid FROM message WHERE messageid = '2101'));
INSERT INTO empuser VALUES(empid_seq.nextval, 'Spock', 'Spock?', '+447911123466', '401', '203', (SELECT messageid FROM message WHERE messageid = '2103'));
INSERT INTO empuser VALUES(empid_seq.nextval, 'Leonard', 'McCoy', '+447911144456', '401', '203', (SELECT messageid FROM message WHERE messageid = '2104'));
INSERT INTO empuser VALUES(empid_seq.nextval, 'Hikaru', 'Sulu', '+447911123856', '401', '203', (SELECT messageid FROM message WHERE messageid = '2106'));

What I want is for the ID to auto Increment on user input.

Upvotes: 1

Views: 399

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

comparison for the if statement has the main problem which might be :

if <some condition> = <other condition> then 
 :new.empuserid := empid_seq.nextval;
end if;

And then you may try below :

Because there's only one candidate column as to be the emp user id is the first column of the table, it's understandable from the insert statement has no column listing for empuser table, then seems you're trying to set the emp user id by sequence with two ways

Get rid one of them :

remove

:new.empuserid.nextval into :new.empuserid from dual;, i.e. remove trigger.

or

empid_seq.nextval, inside insert statement, and transfer into the trigger as

:new.empuserid := empid_seq.nextval; by the way list all columns explicitly, except empuserid in the insert statement as INSERT INTO empuser(col2,col3...) VALUES. Convert these insert statements like

INSERT INTO empuser
SELECT empid_seq.nextval, 'James', 'Kirk', '+447911123456', '401', '203',
       messageid 
  FROM message 
 WHERE messageid = '2101'

Upvotes: 2

Related Questions