Apache OpenOffice Community Forum

Posted: **Mon Jul 20, 2020 3:19 am**

Hi,

Trying to run a script to set a trigger from a button, below is the contents of the scriptfile

CREATE TRIGGER "delete_tblJob" BEFORE DELETE ON "tblJob"
REFERENCING OLD AS oldrow
FOR EACH ROW WHEN (oldrow."jobid"  < 300000)
BEGIN ATOMIC
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invoices can not be deleted, only cancelled';
END;

The code works from Tools, SQL and the code to run the script also works, as it is used with another script which just clears and resets tables.
Is there anything in the code missing?
Prefer running a simple script instead of embedding the sql statement in a macro.
Thanks for your thoughts

Posted: **Mon Jul 20, 2020 4:49 am**

Just looking at the code you included above . . . your last line is:

Code: Select all

END;

The final semi-colon ; should be eliminated ( not present ), put another way, it should be:

Code: Select all

END

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Posted: **Mon Jul 20, 2020 7:13 am**

@Sliderule

Thank you, however removing the semicolon does not make a difference, might have to fiddle with lots of double quotes in a macro instead.

Posted: **Mon Jul 20, 2020 6:29 pm**

Hello,
this works for me, Starbasic, HSQLDB 2.5.1

Code: Select all

SUB SET_TRIGGER
    oController = thisdatabasedocument.Currentcontroller
    if not oController.isConnected then oController.Connect
    oConnection = thisdatabasedocument.Currentcontroller.ActiveConnection
    oStatement = oConnection.createStatement
    sSQL = "CREATE TRIGGER ""delete_tblJob"" BEFORE DELETE ON ""tblJob"" "&_
        "REFERENCING OLD AS OLDROW "&_
        "FOR EACH ROW WHEN (OLDROW.""jobid""  < 30) "&_
         "BEGIN ATOMIC "&_
            "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invoices can not be deleted, only cancelled'; "&_
        "END"
     print sSQL
    oStatement.executeupdate(sSQL)
end sub

SUB DROP_TRIGGER
    oController = thisdatabasedocument.Currentcontroller
    if not oController.isConnected then oController.Connect
    oConnection = thisdatabasedocument.Currentcontroller.ActiveConnection
    oStatement = oConnection.createStatement
    sSQL = "DROP TRIGGER ""delete_tblJob"""
    oStatement.executeupdate(sSQL)
end sub

R

Posted: **Mon Jul 20, 2020 9:07 pm**

@F3K Total,

Thank you very much for this, working like a treat !

Posted: **Tue Jul 21, 2020 6:43 pm**

Just as an FYI ( For Your Information ) I would suggest a small modification to the StarBasic macros suggested by wise user F3K Total above.

Since, CREATING or DROPPING a Trigger might fail if it exists ( on a CREATE ), or, does not exist ( on a DROP ) . . . therefore . . . make that decision in code prior to taking the action.

The following Query will return either integer zero or one . . . so . . . incorporate the query into the macro to determine it the CREATE or DROP should be executed.

Code: Select all

Select 
   COUNT(*) as NUMBER_OF_TRIGGERS 
From INFORMATION_SCHEMA.TRIGGERS 
Where INFORMATION_SCHEMA.TRIGGERS.TRIGGER_SCHEMA = CURRENT_SCHEMA 
  and INFORMATION_SCHEMA.TRIGGERS.TRIGGER_NAME = 'delete_tblJob'

Of course, I am sure you will be able to 'code' and run the above SQL Query and add the 'logic' to the code.

Sliderule

Posted: **Wed Jul 22, 2020 3:25 pm**

@Sliderule - thanks for that

Apache OpenOffice Community Forum

[Solved] SQL Error - Unexpected end of statement

[Solved] SQL Error - Unexpected end of statement

Re: SQL Error - unexpected end of statement

Re: SQL Error - unexpected end of statement

Re: SQL Error - unexpected end of statement

Re: SQL Error - unexpected end of statement

Re: [Solved] SQL Error - Unexpected end of statement

Re: [Solved] SQL Error - Unexpected end of statement