Thunderstone Support Forums

I need more details on how to construct the shell command for the trigger statement. How do I reference the table containing the rows that initiate the trigger?

For example, if I want a trigger that, when rows are deleted in a table, deletes rows in a related table, I would write:

"create trigger del_related after delete on tbl shell 'texis "delete from reltbl where id = [???]"'"

Do I have to include the "texis" keyword?
If so, which parameters are required and which are assumed? And do I nest the quotation marks as shown?

Thanks,
David

First of all you want to set triggermode=1 before creating the trigger, which will allow use of the table name in the command line. You do need to include which command to run, which could be tsql or texis, or some other shell script. In your case you probably want something like:

create trigger del_related after delete on tbl shell 'tsql -d $db "delete from reltbl where id in (select id from $table)" '

You do need the double quotes nested in there, which means you'll need to execute this from the tsql prompt, or build up the query first in Vortex to allow both single and double quotes in the <SQL>

I am trying to write a trigger on a table so I can know what record is updated. I have created an extra table in my database and called it triggers.

Here is an example of how I am thinking it should work.

set triggermode=1;
create trigger user_update_T after update on users FOR EACH ROW shell 'tsql -d $db "insert into triggers (tablename,record) values ($table,$users.id)"';

How would I create a trigger somewhat like this ?

Close. What you want I think is:

set triggermode=1;
create trigger user_update_T after update on users FOR EACH ROW shell 'tsql -d $db "insert into triggers (tablename,record) select ''users'', id from $table"';

$table is the name of a table in the database that has the values, and not the table that caused the trigger.

For an update trigger, can one be written to pass on only changed columns and not the entire row?

No, in a trigger the table that you get will contain all the fields of the source table. With update a "BEFORE UPDATE" trigger will have the old values, and "AFTER UPDATE" the new ones, however both will have all fields, changed or not.

Ok I almost got it working.

If I rebuild the trigger table to only have the "record" field of type "counter" and my trigger looks like this.

create trigger user_update_T after update on users FOR EACH ROW shell 'tsql -d $db "insert into triggers select id from $table"';

It works great. But I can not get the trigger to accept any varchar value that I want to send it.

(i.e. adding a feild called tablename that is a varchar(8) and trying to insert ''users'' I also would like to include a feild action and set its value to insert, update, or delete)

Also on a side note.
Are trigger errors logged anywhere ? I checked the vortex and error logs and did not see them.

When the trigger does work in displays the out put of the sql into my webpage.
(i.e. record -------+)
How do i turn this off or do i have to send the output of the shell command to somewhere else. (i.e. a log file or something)

Any help you could give would be very welcome.

Create a shell script wrapper around the trigger command to log output and errors is a good idea. You should be able to add literals. It may be easier in the shell script, you just need to be careful with quotes. Above I had two single quotes, not one double quote, around users.

Use the -q option to tsql to suppress headers.

I have tried everything you guys suggested yesteday and I am still having a problem. So here is what I did.

(create my trigger log table)
SQL 1>create table triggers (tablename varchar(8),recordid counter);
tablename recordid
------------+------------+

(create my trigger)
SQL 1>set triggermode=1;
SQL 1>create trigger user_update_T after update on users for each row shell 'tsql -q -d $db "insert into triggers (tablename,recordid) select ''users'',id from $table"';

(select from systrig to make sure everything is cool)
SQL 1>select * from SYSTRIG;
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 tsql -q -d $db "insert into triggers (tablename,recordid) select ''users'',id from $table"

(did an update on my users table)

SQL 1>select * from triggers;
tablename recordid
------------+------------+


If I do not try to use the literal it works fine.