How to write triggers?

[John]

It appears that there is an issue with the single quotes when creating the trigger, as you end up with 2 single quotes in the table. As mentioned above a shell wrapper would help, with the literal in the shell script. We'll take a look into why the escaped single quote isn't passed through correctly.

[philip0]

OK, so here is the wrapper script i wrote:

<script language=vortex>
<db=/local/data/texisdb/boarddb>
<a name=main>Never Called</a>
<a name=update_user>
<strfmt "select id from %s" $where>
<sql row $ret>
<sql novars "insert into triggers (tablename,recordid) values ('users',$id)"></sql>
</sql>

</a>
</script>

and here is my trigger:

Texis Version 03.01.996262822(20010727) Copyright (c) 1988-2001 Thunderstone EPI

TR_NAME TR_TIME TR_EVENT TBNAME TR_ORDER TR_REFS TR_TYPE TR_ACTION TR_EXTRA
------------+------------+------------+------------+------------+------------+------------+------------+------------+
user_update_T A U users 0 22 /usr/local/morph3/bin/texis where=$table /local/data/texisdb/boarddb/trigger/update_user.txt

Everything works find if I do an update from the command line.
Example:

tsql "update users set day='1/1/2003'";

tsql "select * from triggers";
tablename recordid
------------+------------+
users 3c4c3b1f7
users 3c4c64bd8
users 3c4d80e58


But, if I go to my web application and run an update on the users table i get a timeout. The users table gets updated but the triggers table never gets the new data.

the vortex log file gets this error:
000 Jan 23 11:55:32 /vortex/userEditor:144: Timeout
000 Jan 23 11:55:32 [module=/local/sites/board_dev/htdocs/vortex/modules/standard]:703: Timeout


userEditor line 144 is a call to a funtion in the module:
<top>

and line 703 in the module is this sql:
<sql max="1" "select id user_id,password
from users
where username = $User">
</sql>

[John]

The CGI environment variables are tripping up the Texis. I was thinking of a shell script, e.g.

#!/bin/sh

/usr/local/morph3/bin/tsql -q -d "$1" "insert into into triggers values select 'users', id from $2"

And the trigger action would be '/path/to/script $db $table'

[chand012]

I'm finally trying this trigger stuff, and having problems. When I try this:

bash-2.00$ texis "create trigger del_patron_orders after delete on patrons for each row shell 'tsql "delete from orders where PID in (select PID from patrons)"'"

I get a syntax error:

bash: syntax error near unexpected token `(s'

When I try this:

bash-2.00$ texis "create trigger del_patron_orders after delete on patrons for each row shell 'tsql ''delete from orders where PID in (select PID from patrons)'''"

the system accepts the trigger, but I get a shell error when the trigger executes:

sh: syntax error at line 1: `(' unexpected

[mark]

You're encountering problems with your command interpreter (shell) interpreting quotes. It may be better for you encode your trigger as a shell script as described above to avoid the quoting problems. Otherwise you'll need to do some creative escaping of the quotes with \ so that the correct thing gets passed to each level of shell.

[chand012]

OK, now I've tried it in both a shell script and a vortex script, and I'm still having problems. I've seen references in this thread to "$table". Is that literal or does it stand for the name, which I should use. In others words, what precisely is the SQL syntax for the shell command? I've tried numerous variations, with and without the variable, in shell scripts, in Vortex scripts--and all fail for various reasons. Are there Texis version issues?

I want the trigger, when a record is deleted from the "patrons" table, to delete all related records in the "orders" table.

Thanks,
David

[mark]

$db and $table may be used in the create trigger statement and will be replaced with the names of the database and temp table respectively when triggermode is 1. See triggermode http://www.thunderstone.com/site/texisman/node154.html

So your trigger action might be, as above:
... shell '/path/to/script $db $table'

The file /path/to/script might contain

#!/bin/sh
/usr/local/morph3/bin/tsql -q -d "$1" "delete from orders where PID in (select PID from $2)"

[josmani]

I have created an update trigger on a table as suggested in this article. I can see the trigger is being fired when I update a record but I get this error message and the shell script does not run.

002 Can't open KDBF file C:\TEMP\trigAAA.AAA.blb for writing: error 2: No such file or directory

Any ideas?

[John]

You need to make sure that either you create the C:\TEMP directory, or make sure that the TMP or TEMP environment variables point to an existing directory.

[josmani]

The directory exists and TMP as well as TEMP environment variables point to that directory.