Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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.

asked 21 Mar '23, 05:52

Mobodon's gravatar image

Mobodon
45115
accept rate: 0%

2

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.

(21 Mar '23, 05:59) Volker Barth

ok, I didn't know that!

(21 Mar '23, 10:22) Mobodon
Replies hidden

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...

(21 Mar '23, 11:23) Volker Barth

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.

(21 Mar '23, 11:35) Chris Keating

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.

permanent link

answered 22 Mar '23, 10:02

MJSobol's gravatar image

MJSobol
91236
accept rate: 100%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×79
×30

question asked: 21 Mar '23, 05:52

question was seen: 786 times

last updated: 22 Mar '23, 10:02