db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4404) Creating trigger throws error in embedded derby
Date Sun, 11 Oct 2009 03:14:31 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4404?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12764412#action_12764412
] 

Dag H. Wanvik commented on DERBY-4404:
--------------------------------------

What does the method methodLoadingScriptFromFile? It can only be a single statement text.
execute can't run scripts. 
These two statements worked for me:
            s.execute("CREATE TABLE groups (" +
                      "id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY
KEY," +
                      "name VARCHAR(32) NOT NULL," +
                      "description VARCHAR(64)," +
                      "colour INT NOT NULL," +
                      "isDefault INT NOT NULL," +
                      "isPermanent INT NOT NULL" +
                      ") ");
            s.execute("CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups " +
                      "REFERENCING NEW ROW AS mod " +
                      "FOR EACH ROW MODE DB2SQL " +
                      "UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id
= mod.id)) AND (mod.isDefault = 1))");

I do see the error if I neglect to add space at the end of first line (after "groups"). Could
that be your problem if the statement is read from a file?



> Creating trigger throws error in embedded derby
> -----------------------------------------------
>
>                 Key: DERBY-4404
>                 URL: https://issues.apache.org/jira/browse/DERBY-4404
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.1.1
>         Environment: Ubuntu 8.04 (GNU/Linux), IcedTea6
>            Reporter: A Hunt
>
> Trying to create a trigger throws an error on embedded Derby, the same trigger can be
created fine using ij. When trying to create the trigger, the error thrown states:
> Syntax error: Encountered "NEW" at line 1, column 69.
> The trigger statement used:
> CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups
> REFERENCING NEW AS mod
> FOR EACH ROW MODE DB2SQL
> UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault
= 1))
> I have created the database beforehand and set up the table groups using:
> connect 'jdbc:derby:MyDbTest;create=true';
> (con = DriverManager.getConnection(connectionURL) for the embedded program)
> Then I run the statement:
> CREATE TABLE groups (
> 	id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY,
> 	name VARCHAR(32) NOT NULL,
> 	description VARCHAR(64),
> 	colour INT NOT NULL,
> 	isDefault INT NOT NULL,
> 	isPermanent INT NOT NULL
> )
> In java the statements are run using s.execute(methodLoadingScriptFromFile());
> I have also tried setting up a new statement for the running of the trigger creation
statement, which didn't help.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message