[EMAIL PROTECTED] wrote: > Hello: > > I am trying to port some functionality from ORACLE to SAPDB with > sequences and triggers. > > All I really want to do is increment the 'FACEID' of the base > table via > the sequence (FACEIDSEQ) in the body of the trigger, but I can't seem > to get the syntax right in order for the trigger to compile > or run. If > someone could point out the problem with my approach (or code) --- it > would be much appreciated. > > JS > > > #-------SQL follows --------------- > > CREATE SEQUENCE FACEIDSEQ > START WITH 1 > INCREMENT BY 1 > NOMAXVALUE > CACHE 10; > > CREATE TABLE FACES > ( > FACEID > > INTEGER PRIMARY KEY, > PICTUREID > > INTEGER , > IMAGE_BLOB > > LONG BYTE, > FACEARRAY > > LONG BYTE > ) > ; > CREATE INDEX ixFACES_PICTUREID ON FACES (PICTUREID) ; > > > CREATE TRIGGER trigface FOR FACES > AFTER INSERT EXECUTE > ( > BEGIN > IF :NEW.FACEID IS NULL THEN > SELECT FACES.FACEIDSEQ.NEXTVAL INTO :NEW.FACEID FROM DUAL; > END; > ) >
You made several errors and did not check in the reference manual for the correct syntax: 1. IF :NEW.FACEID IS NULL THEN is incorrect. see: :NEW and :OLD must always be used with a colon in SQL statements that are used in triggers and that belong to the routine_sql_statements (For example: UPDATE reisen.raum SET hnr = :NEW.hnr WHERE hnr = :OLD.hnr). NEW and OLD must always be used without a colon in SQL statements that are used in triggers and that do not belong to the routine_sql_statements (For example: IF NEW.hnr <> OLD.hnr). BTW: new with INSERT is superfluous. 2. FROM DUAL; is incorrect. see: The tables in the SQL statements of the database procedure must always be complete, i.e. with the owner specified. In the case of SELECT statements, a full statement of the table name in the FROM clause is sufficient. sysdba.dual would be correct. 3. FACES.FACEIDSEQ.NEXTVAL is incorrect. That is neither SQL-Standard, nor Oracle-like. see: [<owner>.]<sequence name>.NEXTVAL Next value generated for the specified sequence name (of the owner in question). And you used the table name in front. 4. you ask for the value NULL for a primary key column. This can NEVER become true. Primary key columns are always NOT NULL 5. In the end: why don't say INSERT into FACES VALUES (FACEIDSEQ.NEXTVAL, ...) A trigger is a very strange way to insert the next value of a sequence. And I am nearly sure, that it was NOT done with triggers when Oracle was used. After this I can do my normal work again. I would like to ask you to check our manuals (reference manuals and the 'how to' use number generators) a little bit more carefully in the future. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
