So I've been doing T-SQL for about 4 years now and consider myself an average scripter. I got asked to do a "simple" trigger on Pervasive SQL and got told it's "similar" to T-SQL. What a load of hogwash. Something that could've taken me an hour to do and test in T-SQL has now taken me 2 days of hair-pulling and tantrum throwing.

This is my "simplified" task in pseudocode:

CREATE TRIGGER on Table
AFTER UPDATE
IF UPDATED (Updated.Field1) --I'm not sure how to do this in pervasive yet
SET Field2 = Updated.Field1, Field3 = Updated.Field1, Field4 = Updated.Field1, Field5 = Updated.Field1
WHERE Field1 = Updated.Field1

I eventually got it right to run the trigger without any errors so wanted to test to see if it actually works (which I know I'm probably going to need help with too). I wrote a "simple" update script:

UPDATE Table
SET Field1 = variable1 WHERE Field98 = variable2 and Field99 = variable 3

I got the following error: "ODBC Error: SQLSATE = S1000, Native error code = 10000"

No matter what I did I kept getting the error. I started googling thinking my syntax was wrong and looking at various forums (dbforums often being one of the sites I look at) but no luck. I crytically came across what seems to be a fix list for Service Pack 3 which says "Update SQL statement returns "Native Error Code 10000" when simple update trigger is on table" on the pervasive site.

I'm on version 8.60.192.030. How do I know what service pack that is (I'm really clueless when it comes to Pervasive SQL)? Also, if I put the service pack on (if that is the problem) will the application that uses it still run?

Any insights would be most helpful. It would be nice to be able to do work on the ACTUAL trigger. *Frustrated*

First, 8.60 is PSQL V8 SP2. PSQL V8.7 is SP3.
Second, why are you needing to do a trigger? In PSQL, once a trigger is defined on a table, Btrieve access is disabled. For example, you list an "AFTER UPDATE" in the CREATE TRIGGER. Once this trigger is created, Btrieve applications will not be able to update that table. Only SQL based apps will be able to update the table.
Post your actual CREATE TRIGGER statement. Pseudocode won't help if there's a syntax error.

Thanks for the clarification.

The product (I don't know if I can be specific on this forum) that the client is using saves average cost for stock. You can set the average stock ONCE only. Thereafter whenever the client wants to change it, it doesn't change. All I want to do is when he sets the average stock, it will update all the necessary columns regardless of how many times he changes it.

I am doing this on a demo database to first get my syntax correct before I "attempt" it on the actual client's database. The initial test trigger I wrote is as follows (and I know it needs work):

CREATE TRIGGER Update
AFTER UPDATE on Enrolls FOR EACH ROW
UPDATE Enrolls SET Grade = Class_ID WHERE Student_ID = OLD.Student_ID;

I then run an update script to test to see if it works:

UPDATE Enrolls
SET Class_ID = 58 where Student_ID = '107157151' and Class_ID = 27;

This update statement works fine if the trigger is not on the table.

If I were to do this in T-SQL essentially I'd write the following trigger:

CREATE TRIGGER Update
ON Enrolls
FOR UPDATE AS
IF UPDATE(Class_ID)
BEGIN
SET Grade = Class_ID WHERE Student_ID = (select Student_ID from Inserted);
END
GO

The other alternative I have if creating the trigger is going to be impossible to do is to create a Stored Procedure and schedule it to run once a day. BUT unlike SQL2000, Pervasive doesn't seem to allow scheduling. Is there a way I can create a Scheduled Task in Windows that will interact with Pervasive and schedule the Stored Procedure for me?

I don't have a PSQL V8 machine available so I can't testlike you are. Here's the link to the PSQL Documentation for the CREATE TRIGGER:
http://www.pervasive.com/library/doc...ntaxref18.html

Again, if the application is Btrieve based then Triggers will cause problems.

As far as scheduling, no. PSQL doesn't offer any scheduling. You might be able to write a small program that calls a Stored Procedure (or just issues the queries directly) and then schedule that using Window's Scheduler.

*sigh*

Lets scrap the trigger then and look at the alternative.

I have written a stored procedure which works. :-)

I assume I need to write some kind of .bat file which would connect to the odbc and login and then call the procedure, and which in turn I will then run from the Windows Task Scheduler? You don't perhaps have an example of a batch file that could do this? *hopeful*

My experience with anything outside of SQL is sketchy to say the least and I've outgoogled the internet without any luck so far. :-(

Well, I don't have an example of a batch file but you might look at PvQuery.EXE from the Pervasive Component Zone (http://www.pervasive.com/ComponentZo...+%2F+Utilities).
It is a command line tool that takes an SQL file as a parameter and executes the statement(s) in the file.

I've used it during an install procedure to create the sample database.

THANK YOU!!!!!!

I eventually "figured out" how to use it and then incorporated it into a batch file which I then scheduled with Task Scheduler. It works!!!

Now to use it on actual data - I'm holding thumbs but I'm 99% closer to a solution.

Thank you very much!

Glad you got it working..

Do you have sample copy of script ? does your script creates result set that gets save to test file?

Do you have sample copy of script ? does your script creates result set that gets save to test file?

Do you have sample copy of script ? does your script generates result set (text file)?

@maccs2000,
Why did you bring up a thread from 3 years ago? You really should have started a new thread.

Start a new thread with the problem you are currently having. Put as much detail as you can. You can post a link back to this thread.

I'm going to lock this thread.