Trigger problem

barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Trigger problem

Post by barry.marcus »

In my database I have a table called project and a table called logproject. I want to create a trigger on project that inserts a row into logproject with each insert into project. Here are the statements I used, at the TSQL prompt, to create the trigger:

set triggermode=1;
create trigger project_insert_T after insert on project FOR EACH ROW shell 'tsql -d $db "insert into logproject (logid, tablename, id, p_equivs, c_equivs, name) select counter, ''project'', id, p_equivs, c_equivs, name from $table"';

Nothing gets inserted into logproject when I insert data into the project table, either via Vortex code or at the TSQL prompt. That is, the following SQL statement does not seem to trigger the trigger:

insert into project (id, p_equivs, c_equivs, name) values (counter, 'none', 'none', 'MyProject');

Not sure what I'm doing wrong.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Trigger problem

Post by barry.marcus »

BTW... Yes, those are pairs of single quotes before and after the constant "project" in the select statement, and the shell command terminates with single quote followed by a double quote.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Trigger problem

Post by mark »

Is tsql in the PATH of the process performing the insert? If not adjust the PATH or use the full path to tsql in the trigger.

Does $db have any spaces in it? If so you'll need to put quotes around that.

Do you get any errors from the process performing the insert?
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Trigger problem

Post by barry.marcus »

Yes, C:\MORPH3, which is where tsql.exe is installed, is in system PATH, and I have verified that I can execute tsql from any directory. But just to be sure, I recreated the trigger with the full path to tsql.exe.

The path to the database location does not have any spaces, but I used the literal path to the database also, just to be sure.

It still does not work. And as far I can tell, the Vortex script that performs the INSERT INTO project generates no errors (either in the vortex log or in the page source). Nor do I get any errors when I issue an INSERT INTO at the TSQL prompt. The row is inserted into the project table, but the logproject table does not get INSERTed INTO in either case.

Here are the statements that I'm now using to create the trigger:

set triggermode=1;
CREATE TRIGGER project_insert_T BEFORE INSERT ON project FOR EACH ROW shell 'C:\MORPH3\tsql.exe -d D:\Crosshairs\database1 "insert into logproject (logid, tablename, id, p_equivs, c_equivs, name) select counter, ''project'', id, p_equivs, c_equivs, name from $table"';
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Trigger problem

Post by John »

There appears to be an issue with the single quotes not being unescaped properly. You may need to workaround by execing a Vortex script that takes variables on the command line instead.
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Trigger problem

Post by barry.marcus »

I'm not really sure what you mean. If you could elaborate a bit, and maybe provide a small example, that would be great.

But in the meantime, in light of your observation, here's what I tried... I changed the INSERT INTO statement in the trigger definition so that it no longer involves embedded doubled-up (i.e., escaped) single quotes, since I can easily infer the source tablename (i.e., "project") from the log table name anyway (which is "logproject"). The new trigger definition is as follows:

set triggermode=1;
CREATE TRIGGER project_insert_T AFTER INSERT ON project FOR EACH ROW shell 'C:\MORPH3\tsql.exe -d D:\Crosshairs\database1 "insert into logproject (logid, id, p_equivs, c_equivs, name) select counter, id, p_equivs, c_equivs, name from $table"';

(BTW, I mistakenly reported earlier that I had used BEFORE INSERT in the trigger definition. I did not... It uses AFTER INSERT.)

Now the trigger DOES fire on insert into the project table (change is good, right?), however the values inserted into logproject are not correct and I get the following message:

100 (null) is not a valid field

The values inserted into project are
id: 4ca1fe2a2
p_equivs: none
c_equivs: none
name: AnotherProj

whereas the values inserted into logproject are
logid: 000000000
id: 000000000
p_equivs: <this field is null>
c_equivs: <this field is null>
name: <this field is null>
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Trigger problem

Post by John »

Triggering a Vortex script, e.g.

... SHELL 'texis.exe -r db=$db table=$table tbname=project scriptname/main.txt'

A vortex script something like:

<script language=vortex>
<a name=main>
<DB=$db>
<sql row "select * from " $table>
<sql novars "insert into logproject(logid, tablename, id, p_equivs, c_equivs, name) values (counter, $tbname, $id, $p_equivs, $c_equivs, $name)">
</sql>
</sql>
</a>
</script>
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Trigger problem

Post by barry.marcus »

Thanks John. I'll give that a try.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Trigger problem

Post by barry.marcus »

OK. Almost there... Maybe this is a dumb question, but...

In which directory do I locate the vortex script? And if the name of the script is, say, populatelog then should the trigger still contain "/main.txt"?
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Trigger problem

Post by barry.marcus »

I placed the script, called populatelog, in the database directory itself, and that works fine.

But I have one more question... When the trigger fires as the result of an insert at the TSQL prompt, the script called by the trigger works properly. However, when the trigger fires as the result of an insert initiated by our Vortex script, the trigger does not work and I get an error in the log, as follows:

populatelog: (4452) Can't write stdout: Bad file descriptor; exiting

Could this be because, when initiated at the TSQL prompt, the following text is sent to the command window when the trigger fires:

<!-- Texis RDBMS Copyright (c) 1992 - 2010 Thunderstone - EPI, Inc. -->
<!-- Texis Web Script Copyright (c) 1996 - 2010 Thunderstone - EPI, Inc. -->

If this is the problem, how do I suppress this output, or redirect it to where it can be written?
Post Reply