Bluemix SQL Database service and triggers

Question

I'm not able to create triggers on the SQL Database Service on Bluemix. I always get the error message "queryExecFailure - Unupported SQL statement or command." Which is clear enough. Does someone got the same feedback ? Any option to activate triggers ?

Many thx

What service do you use, free or premium? How did you execute the CREATE TRIGGER? — data_henrik
– data_henrik, Commented Jan 7, 2016 at 14:24
I'm using the free service and sending SQL command via the bluemix interface of the SQL Database service (SQL Database console -> Run queries). Query is the following : — renaudb
– renaudb, Commented Jan 20, 2016 at 16:49
CREATE TRIGGER USER11266.CI_IT_VOITURESCAST after insert on USER11266.VOITURESCAST for each row begin insert into USER11266.CI_BT_VOITURESCAST ( IH_PK ,IH_OPERATION_NAME ,IH_FLAG ) values ( CI_SEQ_VOITURESCAST.nextval ,'INSERT' ,NULL ); end — renaudb
– renaudb, Commented Jan 20, 2016 at 16:50
As I said in my answer, you cannot run DML through the query interface. — data_henrik
– data_henrik, Commented Jan 24, 2016 at 9:23

data_henrik · Accepted Answer · 2016-01-24 09:26:38Z

1

There are several feature restrictions in the SQL DB service on Bluemix compared to a regular DB2 database. They depend on whether you use the free service or the premium service. In the free service the user is mapped to a schema within a DB2 database and only has basic privileges. This cuts down some possible use cases for triggers.

Creating triggers is DDL, not DML. Success creating them depends on how the statement is executed. The "Run Query" interface can only be used to execute SELECT statements. To run something like a trigger you would need to connect a SQL client (e.g., IBM Data Studio, SQuirreL, ...) to the database.

Also note the required privileges to create triggers. The power of triggers is limited to your schema within and your privileges.

edited Jan 24, 2016 at 9:26

answered Jan 7, 2016 at 14:15

data_henrik

17.3k2 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dave L. Over a year ago

This answer seems somewhat ambiguous. Are triggers disallowed in the free version or not?

data_henrik Over a year ago

Triggers are allowed, but with some restrictions because of the setup.

Collectives™ on Stack Overflow

Bluemix SQL Database service and triggers

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related