Trigger Limitations

[Former Member]

The following

CREATE TABLE test1.sales (region VARCHAR(100), product VARCHAR(100), amount INTEGER);

CREATE TRIGGER test1.sales_trigger1

AFTER INSERT ON test1.sales

BEGIN

   DECLARE l_total INTEGER;

   SELECT SUM(amount) INTO l_total FROM test1.sales;

END;

fails with

SAP DBTech JDBC: [451]: modification of subject table in trigger not allowed: TEST1.SALES

and the docs SAP HANA Reference: CREATE TRIGGER say

• Access of subject table which is table that trigger is defined on, is not allowed in trigger body,
  which means any insert/update/delete/replace/select for table which trigger is on is not allowed.
• Only row level trigger is supported. Statement level trigger is not supported.
  Row level trigger means trigger action will be executed for every row changes.
  Statement level trigger means trigger action will be executed once for a statement execution.
  Syntax 'FOR EACH ROW' means row-wise trigger execution and it is default mode. Even when 'FOR EACH ROW' is not defined, it is still row level trigger.

This is a major limitation .. seriously. Any plans if and when this will be implemented?

[RichHeilman · Accepted solution]

Triggers in SP4 is a limited implementation, and we are working to enhance this feature in SP5. 

1) In your original post, you have SELECT against test1.sales and it gives error as modification of trigger, this is a bug, and development is working on fixing this soon via a revision.  There are no plans to support modification(insert, update, delete) of subject table within the trigger.

2) Currently there is no plan to support statement-level triggers.

3) Multiple triggers is now supported, I'm in the process of finding out which revision this was shipped with.

Cheers,

Rich Heilman

HANA Product Management

[Former Member · Accepted solution]

Thanks Rich,

Rgd 1): Ah, ok. And: lack of DML is fine for me .. usually not needed / hard to get right or understand anyway.

Rgd 2): ;(

Rgd 3): Great!

Should you get back on this, could you ask for hints on

https://scn.sap.com/message/15557594#15557594

also? I guess that should work, but I can't figure out how ..

Cheers,

Tobias

[RichHeilman · Accepted solution]

Just one correction about the point on multiple trigger support.  This is supported, but not yet released.  It will be released in an upcoming revision.

Cheers,

Rich

[virenp_devi · Accepted solution]

Hello Rich,

I am using SP6 and trying to execute below,

**************

create trigger VDTRIGGER

AFTER INSERT ON "VDSCHEMA"."SALES_FACT" for each row

begin

INSERT INTO "VDSCHEMA"."AUDIT" values(current_timestamp, current_user)

end;

****************

it still gives similar errors like below

Could not execute 'create trigger VDTRIGGER AFTER INSERT ON "VDSCHEMA"."SALES_FACT" for each row begin INSERT INTO ...' in 2 ms 729 µs .

SAP DBTech JDBC: [257] (at 157): sql syntax error: incorrect syntax near "end": line 5 col 1 (at pos 157)

Kindly suggest.

I am using cloudshare by SAP and SAP HANA SPS6 server.

Regards,

Vire

[former_member182090 · Accepted solution]

Looks like you forgot a semicolon at the end of the INSERT statement?

HTH,

Rob V.

[Former Member]

Here is the fun part: when I split out the logic into another stored procedure and only call that from within the trigger, I can access the trigger subject table. And the trigger only fires once: per statement.

I am now confused.

[Former Member]

More fun:

1) The docs regarding

CREATE TRIGGER TEST_TRIGGER_VAR_UPDATE
AFTER UPDATE ON TARGET
REFERENCING NEW ROW mynewrow, OLD ROW myoldrow

FOR EACH ROW  

are buggy. The accepted syntax is

REFERENCING NEW AS mynewrow, OLD AS myoldrow

2) Only 1 Trigger per table is supported:

SAP DBTech JDBC: [7] (at 53): feature not supported: more than one trigger for single table is not yet supported: line 2 col 22 (at pos 53)