Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Trigger Syntax

  1. #1
    Join Date
    Oct 2011
    Posts
    12

    Red face Unanswered: Trigger Syntax

    Hi,

    I am using Pervasive Sql version .... 9.60.016.000.

    MY trigger looks as follows:

    CREATE TRIGGER "MMSPO"

    ON HistoryLines
    AFTER INSERT
    AS
    BEGIN

    SET NOCOUNT ON;
    declare @documentnumber nvarchar(8);
    declare @itemcode nvarchar(15);
    declare @ddate Date;
    declare @unitused nvarchar(4);
    declare @description nvarchar(40);
    declare @qty double;
    declare @unitprice double;
    declare @taxamt double;
    declare @mms bit;
    declare @buyout bit;
    declare @budget bit;

    select @documentnumber,@itemcode,@ddate,@unitused,@descri ption,@qty,@unitprice,@taxamt,0,0,0
    from inserted where documentnumber like 'PN%' and itemcode like 'RM%' or itemcode like 'BO%';

    INSERT INTO MmsPo (documentnumber,itemcode,ddate,unitused,descriptio n,qty,unitprice,taxamt,mms,buyout,budget)
    values (@documentnumber,@itemcode,@ddate,@unitused,@descr iption,@qty,@unitprice,@taxamt,0,0,0);
    END
    GO

    The error I am getting:

    [LNA][PERVASIVE][ODBC Engine Interface]Syntax Error: CREATE TRIGGER<<???>> MMSPO
    on HistoryLines
    AFTER INSERT
    AS BEGIN
    SET NOCOUNT ON

    Any Ideas??
    Thanks

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    That looks like SQL Server syntax. PSQL does not use the same syntax for triggers or stored procedures.
    Things like NOCOUNT don't exist in PSQL. The DECLARE statements are like:
    DECLARE :ddate date;
    Docs can be found at Pervasive's web site.

    One very important thing to know about triggers in PSQL:
    When you create a trigger, Btrieve access for that operation is disabled. For example, if you create an INSERT trigger on a table, a Btrieve based application will receive a status 149 which is defined as:
    149: SQL Trigger
    While using the Btrieve API to alter database tables or entries, the system encountered SQL restrictions placed on the database by the SQL layer.
    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
    Oct 2011
    Posts
    12

    Question Trigger Syntax

    Thanks for the response. So actually you said that the trigger will give me errors via the Betrieve app.

    We are using Pastel accounting.....any suggestions how to populate that temp table. Our Manufacturing App is written in VB.Net using a SQL db and I need the info to be available in this app. I did setup a linked server with views to the HistoryLines table, but somehow I started to get an error from the view that the Date, DateTime field is not valid.

    OLE DB provider "MSDASQL" for linked server "Pioneer" returned message "[Pervasive][ODBC Client Interface]Invalid date, time or timestamp value.".
    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "Pioneer".

    Although, I do not use the DDate field in the view or any other Date, DateTime field.

    When I Populate the MMSPO table in Pervasive, The view in SQL is working perfect.

    Any Suggestions.
    Thanks Again.
    Last edited by SoftSpot; 11-01-11 at 03:13.

  4. #4
    Join Date
    Oct 2011
    Posts
    12

    Exclamation Trigger Syntax

    Just to make it more clear. I created a view from SQL and the Linked Server on the MMSPO table in Pervasive. The view on the Pervasive HistoryLines table is still giving me errors.

    Thanks

  5. #5
    Join Date
    Oct 2011
    Posts
    12

    Question Trigger Syntax

    If the trigger is going to give me trouble, I can always use a Stored Procedure that run once a day.

    The only thing that I do not know at this stage, is how to fire the store procedure once a day. Can I fire it from a VB app, as I am having a background app running on the server, doing some work on the SQL db.

    Thanks

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    You can fire a stored procedure from a VB app. Simply open an ODBC connection from the VB app to the PSQL database and execute the SP. Another option is a scheduled task using a command line tool like PvQuery.
    As for the "invalid date..." error, it sounds like you've got a date, datetime, or timestamp field in the query and the value in the database is not valid. Usually this is caused by a Btrieve app that writes something like 0000-00-00 to the database which ODBC treats as invalid rather than null. Check your query, run it in the PCC, and see if it throws any errors. Check each of the fields to find out if one of them is a date, datetime, or timestamp and verify the data is valid for each record.
    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
    Oct 2011
    Posts
    12

    Trigger Syntax

    Thanks again for the help.

    If I run the query in PCC, I am getting all the records. It is the query in my SQL view that give the error...even if I query it without all the dat, time and datetime fields...Strange.

    I will work on the Stored Procedure and see where I end up.

    Thanks again.

  8. #8
    Join Date
    Oct 2011
    Posts
    12

    Red face Stored Procedure

    Hi,

    I am running Pervasive version 9.6

    I am totally lost with the Syntax of a SP..even when I looked at the manual you posted me before.

    Please help me with the following SP:

    CREATE PROCEDURE BuyOutPriceCheck

    BEGIN
    INSERT INTO MmsPo (documentnumber,itemcode,ddate,unitused,descriptio n,qty,unitprice,taxamt,mms,buyout,budget)
    select documentnumber,itemcode,ddate,unitused,description ,qty,unitprice,taxamt,0,1,0
    from historylines where documentnumber like 'PN%' and itemcode like 'BO%';
    END;

    I also need a similar SP that will do the same but where DDATE = Current Date.

    The error I am getting is:
    [LNA][Pervasive][ODBC Engine Interface][Data Record Manager][SPEng]3: 'BEGIN' Syntax Error.

    Thanks again in advance.

  9. #9
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    This syntax created the SP for me:
    Code:
    CREATE PROCEDURE BuyOutPriceCheck();
    BEGIN
    INSERT INTO MmsPo (documentnumber,itemcode,ddate,unitused,description,qty,unitprice,taxamt,mms,buyout,budget)
    select documentnumber,itemcode,ddate,unitused,description ,qty,unitprice,taxamt,0,1,0
    from historylines where documentnumber like 'PN%' and itemcode like 'BO%';
    END;
    I couldn't run it because I don't have the tables. Once you have the first SP, you should be able to create the second with your DDATE restriction.
    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.

  10. #10
    Join Date
    Oct 2011
    Posts
    12

    Trigger Syntax

    Thanks a million...the syntax seems to be correct now, however I am getting an error when trying to save it:

    The Application encountered a Permission error(Btrieve Error 94).
    I do have access to everything...I can drop and create tables etc.

    Where can I start digging now?

    Thanks Again.

  11. #11
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Which application? What do you mean "save it"?
    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.

  12. #12
    Join Date
    Oct 2011
    Posts
    12

    Trigger Syntax

    Sorry,

    I am in PCC, right click on Store Procedures, New.
    Then I copy the code and click the Save Icon in PCC.

    Then the error follows.

    Thanks

  13. #13
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    The status 94 is a permission error. The engine doesn't believe you have rights to the files for some reason. Specifically, the files you need rights to are the DDF files (FILE.DDF, FIELD.DDF, PROC.DDF, etc).
    Are you running these commands at the server or a client pointing to a remote server?
    Does the database require a login? If so, are you using the Master username/password?
    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.

  14. #14
    Join Date
    Oct 2011
    Posts
    12

    Red face Trigger Syntax

    Ok, here is the complete picture.
    Pastel Accounting is using the Pervasive DB that is on a server. The clients are loaded on each employee's pc that connects via the ethernet network.

    I am working on the server without any client or server software.

    I open PCC, without any username or password, expand Engines, Expand the Server where the db is (local server), Expand Databases, Expand the database where I want to store the SP, Right Click Stored Procedures, New, Store Procedure.

    As I try to save the SP, I encounter the error. I can drop tables and create new tables without any problem. I also must admit that I know nothing about Pervasive.

    If I can populate my temp Table "MMSPO", then I will work with it via SQL server's Linked Server and views.

    Thanks so much for your answers so far.

  15. #15
    Join Date
    Oct 2011
    Posts
    12

    Thumbs up Stored Procedure

    I took a backup of the db after everyone was out...restored it on my machine, and the Stored Procedure worked like a charm.

    It seems that I had a copy of the db while there was still user activity on it.

    Thanks so much for all the help...you are an

Tags for this Thread

Posting Permissions

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