Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Posts
    56

    Unanswered: Trigger on Pervasive SQL 8

    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*

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Mar 2007
    Posts
    56
    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?

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Mar 2007
    Posts
    56
    *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. :-(

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Mar 2007
    Posts
    56
    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!

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Glad you got it working..
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  9. #9
    Join Date
    Sep 2010
    Posts
    3

    Pervasive SP or query that generates output and being called by Batch file

    Quote Originally Posted by Mynx View Post
    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!
    Do you have sample copy of script ? does your script creates result set that gets save to test file?

  10. #10
    Join Date
    Sep 2010
    Posts
    3

    Pervasive SP or query that generates output and being called by Batch file

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

  11. #11
    Join Date
    Sep 2010
    Posts
    3

    Pervasive SP or query that generates output and being called by Batch file

    Quote Originally Posted by Mynx View Post
    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!
    Do you have sample copy of script ? does your script generates result set (text file)?

  12. #12
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    @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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •