-2

oracle :

CREATE OR REPLACE TRIGGER ORA_ADMIN.T_INCOMING_FILE 
BEFORE INSERT ON ORA_ADMIN.INCOMING_FILE FOR EACH ROW
DECLARE
 err_num NUMBER;
 err_msg VARCHAR2(100);
BEGIN
SELECT incoming_file_seq.NEXTVAL into :NEW.ID from dual ;
EXCEPTION
    WHEN OTHERS THEN
  -- throw application error
    err_num := SQLCODE;
    err_msg := SUBSTR(SQLERRM, 1, 100);
  RAISE_APPLICATION_ERROR( 100, 'Insert into incoming_file failed ' ||err_num || err_msg );
END;
/

postgresql : what is the equivalent syntax in postgresql?

  • I fail to see the purpose of the trigger. It only increments the sequence but does nothing else. I can't imagine any situation where that would actually throw an exception (except when you drop the sequence after you created the trigger). If the trigger is supposed to automatically assign an ID to a column in the table, then it's not needed at all in Postgres. Just define that column as serial or generated by default as identity if you are using Postgres 10 – a_horse_with_no_name Jul 5 '18 at 6:52
  • it seems that all this does is enforce default value in the ID column and rewrite the error message - how important is that? – Jasen Jul 5 '18 at 7:18
  • @a_horse_with_no_name perhaps GENERATED ALWAYS is a closer match to the behaviour of that trigger than GENERATED BY DEFAULT – Jasen Jul 5 '18 at 7:25
  • @Jasen: The trigger does not "enforce a default value" because it does not modify the inserted rows in any way. And the generated value of the sequence is never checked (or even included in the error message). The code makes no sense – a_horse_with_no_name Jul 5 '18 at 7:29
0

Both are in pl/sql. So its not a big deal to convert the schema. But still, there are many restrictions to convert these kinds of the trigger. I suggest,

http://www.sqlines.com/online

https://aws.amazon.com/documentation/SchemaConversionTool/ (but this will work on top of OLTP, it'll not convert the queries)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.