|
With SQL Server, I developed a trigger that would be triggered anytime data was added to a certain table, in this instance FORECAST TEST DATA. The trigger was to subsequently insert certain data from the inserted row into a table named the PRODUCT TEST DATE table, as seen in this example. The other columns in the database were then to be populated with values already present in the table, utilizing products with the same PROD NUM value. Query: CREATE OR ALTER TRIGGER FORECAST_TRIGGER ON FORECAST_TEST_DATA FOR INSERT AS INSERT INTO PRODUCT_TEST_DATA (PRODUCT_TEST_DATA.PROD_NUM, PRODUCT_TEST_DATA.MONTH, PRODUCT_TEST_DATA.STORE_TYPE, PRODUCT_TEST_DATA.PRODUCT_KEY, PRODUCT_TEST_DATA.CATEGORY, PRODUCT_TEST_DATA.BRAND_NAME,PRODUCT_TEST_DATA.COLOUR) SELECT inserted.PROD_NUM, inserted.MONTH, inserted.STORE_TYPE, inserted.PRODUCT_KEY, PRODUCT_TEST_DATA.CATEGORY, PRODUCT_TEST_DATA.BRAND_NAME,PRODUCT_TEST_DATA.COLOUR FROM inserted, PRODUCT_TEST_DATA WHERE inserted.PROD_NUM = PRODUCT_TEST_DATA.PROD_NUM GO This issue has hampered my development on the project, and I'm not sure how to proceed. The needed functionality is already included in the trigger; it only has to be rebuilt in Oracle SQL. |
|
Oracle uses :old and :new to determine the "old" values in a table vs "new" values being inserted into a table. You also need to determine if you you want the trigger to fire BEFORE inserting new values, or AFTER the insert. Check online Oracle documentation re: BEFORE and AFTER triggers. Your example should be easy enough to port over to Oracle. |
Please note, this is a forum for questions related to the product SAP SQL Anywhere, not for general SQL questions or topics on Microsoft SQL Server or Oracle. I'd recommend to ask in an appropriate forum.
ok, I didn't know that!
This should not come across as a strict "You must not ask such questions here" rule (note the FAQ), it's just rather improbable that this forum is read by many (or even any?) Oracle experts who are able to answer your question...
Perhaps a web search might help. This trigger should be relatively easy to port to PL/SQL (albeit I have almost no experience with Oracle - any I had is a least a decade old). Oracle does not use AS - PL/SQL trigger logic uses BEGIN and END. I think that "inserted" is :NEW in PL/SQL triggers. Statements need semicolon terminators and GO is not used. Hopefully, this can get you started. Again, I am not PL/SQL fluent and much of this is knowledge of TSQL from a SQL Anywhere context.