Create TRIGGER with TABLE STATEMENT on HANA

[Former Member]

Hi,

I am not able to create a TRIGGER on a column table as soon as I try using a table. This workds fine for the same TRIGGER using rows. But I can not use rows as we have much too many records.

Here is what works fine:

-- Insert TRIGGER ROW
CREATE TRIGGER INSERT_TRIGGER_ROW
AFTER INSERT ON "test.track_delete::create_table.CCR_FINALMARKETID_01"
REFERENCING NEW ROW mynewrow
FOR EACH ROW
BEGIN
INSERT into "S00049863"."test.track_delete::create_table.CCR_FINALMARKETID_01_TRIGGER"
VALUES(:mynewrow."GUID", :mynewrow."CURVE", :mynewrow."DATE",
'', 'I',
CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP)
;
END;

Here is what does not work:

-- Insert TRIGGER TABLE
CREATE TRIGGER INSERT_TRIGGER_TABLE
AFTER INSERT ON "test.track_delete::create_table.CCR_FINALMARKETID_01"
REFERENCING NEW TABLE AS newtable
FOR EACH STATEMENT
BEGIN
INSERT into "S00049863"."test.track_delete::create_table.CCR_FINALMARKETID_01_TRIGGER"
SELECT "GUID", "CURVE", "DATE",
'', 'I',
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
FROM :newtable;
END;

The error message is not about the SQL statement but about the column table:

Could not execute 'CREATE TRIGGER INSERT_TRIGGER_TABLE AFTER INSERT ON ...'
SAP DBTech JDBC: [7]: feature not supported: Cannot create statement trigger on column table: test.track_delete::create_table.CCR_FINALMARKETID_01: line 2 col 18 (at pos 54)

Some help would be very welcome.

Regards,

Laurent

[Former Member]

And I add that the online documentation seems to be open for such TRIGGER:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1604/doc/html/saiq-...

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.00/en-US/20d5a65575191014946db96aae...

[SergioG_TX]

the url

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/1.0.12/en-US/20d5a65575191014946db96aae...

says triggers are only valid on row tables - this is probably why your statement isnt working due to your column table

[neilprince]

The help link above says 'Statement-level triggers are only supported in row-store tables.'. Your trigger has the statement 'FOR EACH STATEMENT' therefore you get this error. If you use 'FOR EACH ROW', it will work on a column table.

Regards

Neil

[former_member614035]

I am confused regarding the for each statement and for each row.Why for each statement works only for row-store trriggers?