Pervasive insert and update triggers do not fire

[mcbeth352001]

I'm running Pervasive 9.5 after creating insert, update, delete triggers and testing them through PCC only the delete trigger fires.

Right now i'm just testing the most simple triggers I can create. I have table A and B, all triggers are on table A and effect table B.

I'm only using pcc or ODBC to test so it shouldn't be a Btriev problem.

What are your thoughts?

 

[mirtheil]

- What exact version of PSQL are you using?
- What are your CREATE TRIGGER statements?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mcbeth352001]

Pervasive version: 9.50.077.002

Update:
--------------------
first I found my error on my simple example. I was referencing old insead of new for an insert.

CREATE TRIGGER test_insert
after insert
on test_trigger
referencing old<---error is here> as n
for each row
insert into slcust (customerid) values('test1 insert')
;

New Problem:
-----------------
I have moved on to starting to test the actual trigger I need to create. This trigger does not fire.

My current trigger is:

;create trigger slcust_UPDATE
AFTER UPDATE
ON slcust
REFERENCING new AS indata
FOR EACH ROW
INSERT INTO slcust_trx(
CustomerID,
Description,
Name,
Address1,
Address2,
City,
State ,
Zip ,
County,
Country,
Contact,
Phone ,
Fax ,
Email,
TypeID,
Active,
OpenDate,
SalespersonID,
RegionID,
CreditType,
CreditStatus,
CreditLimit,
CreditUsed,
CreditBalance,
HighestBalance,
CreditRating ,
PriceListID,
TaxCodeID,
DueTerms,
DueDays,
AuditUserID
)VALUES (indata.CustomerID,
indata.Description,
indata.Name,
indata.Address1,
indata.Address2,
indata.City,
indata.State,
indata.Zip,
indata.County,
indata.Country,
indata.Contact,
indata.Phone,
indata.Fax,
indata.Email,
indata.TypeID,
indata.Active,
indata.OpenDate,
indata.SalespersonID,
indata.RegionID,
indata.CreditType,
indata.CreditStatus,
indata.CreditLimit,
indata.CreditUsed,
indata.CreditBalance,
indata.HighestBalance,
indata.CreditRating,
indata.PriceListID,
indata.TaxCodeID,
indata.DueTerms,
indata.DueDays,
indata.AuditUserID
);

What am I missing?

 

[mirtheil]

I don't see anything obvious. How are you testing it? How are you doing the update (that should fire the trigger)?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mcbeth352001]

;insert into jwsdata1..slcust (customerid, description)
values('test02','testing')

--The trigger should fire after this statment--
;update slcust set description = 'testing tested1'
where customerid = 'test02'

I have also tried changing the trigger to this:
;create trigger slcust_UPDATE
AFTER UPDATE
ON slcust
--REFERENCING new AS n
FOR EACH ROW
INSERT INTO slcust_trx(CustomerID) VALUES(new.CustomerID);

But it still doesn't work.

Any suggestions about how to narrow down the problem

 

[mirtheil]

Can you try this very simple example:

CREATE TABLE A (col1 INTEGER, col2 CHAR(10));

CREATE TABLE B (col1 INTEGER, col2 CHAR(10));

CREATE TRIGGER MyUpdate 
AFTER UPDATE ON A FOR EACH ROW 
INSERT INTO B VALUES (NEW.col1, NEW.col2); 

insert into a (col1, col2) values (1,'1');

update a set col1 = 5;

select * from b;

I ran it and it worked for me. I don't have your data so I can't try yours.
One more thing, by "doesn't work", do you mean that the "slcust_trx" table doesn't have a record for the data?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mcbeth352001]

Correct the slcust_trx table has no data.

I tried you sample on two different pervasive databases A and B. Database 'A' is where it needs to work b/c that is where the slcust table is. Database 'B' is I just created for testing.

You sample still didn't work in DB 'A'. But it worked just find on DB 'B'. I have look high and low for anything that would prevent a database trigger from firing and I found the problem.

note: also in my earlier post when the trigger worked it was also in my test DB 'B'


/******************
Solution
********************/
(all of this must be done from the pervasive database server)
if you right click on the database in PCC and click on the "General" settings in the left hand split screen, you must have the "Integrity Enforced" checked. After checking this you have to restart the pervasive services on your database server.

after doing this your sample worked just fine in DB 'A'

Thanks for your help.

 

[mirtheil]

Glad you got it figured out.. The "Integrity Enforced" is enabled by default, I didn't think about checking that.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com