Using Passthrough Query to Disable Triggers

[jeff]

Hi gurus

I have been successfully using passthrough queries to disable and enable
triggers on a MSSQL database table:-

EXECUTE sp_msforeachtable "ALTER TABLE tablename DISABLE TRIGGER ALL"

EXECUTE sp_msforeachtable "ALTER TABLE tablename ENABLE TRIGGER ALL"

through Linked tables in MSAccess.

The backend has been moved to an ORACLE server and I still need to do the
updates but cannot run the above passthrough queries. I also cannot update
the oracle table columns - i get the error

Write Conflict
The record has been changed etc.....

the error message is not correct.. i believe that the triggers are firing
and raising exceptions that Access doesnt interpret properly...

is there a way I can run a passthrough query to call an appropriate Oracle
Stored Procedure to do the same as MSSQL???

helllllpppppp!!!!

cheers
jeff

 

[Stefan Hoffmann]

hi Jeff,

is there a way I can run a passthrough query to call an appropriate Oracle
Stored Procedure to do the same as MSSQL???

PL/SQL: ALTER TABLE tableName ENABLE|DISABLE ALL TRIGGERS;


mfG
--> stefan <--

 

[jeff]

stefan

thx for your post, how do i run this from an MSACCESS pass through query?

My previous Passthrough Query used the following for MSSQL:-

EXECUTE sp_msforeachtable "ALTER TABLE tablename DISABLE TRIGGER ALL"

I cannot get this to work in the MSAccess pass through query...

PL/SQL: ALTER TABLE tableName DISABLE ALL TRIGGERS;

I get an "..Invalid SQL statement.." error

cheers
Jeff

 

[jeff]

DOH

I AM an idiot..... got it going.. i had my table name wrong AND was
prefixing the SQL with PL/SQL: ... what am i? an idiot.. thx Stefan...

 

[Stefan Hoffmann]

hi Jeff,

DOH

mfG
--> stefan <--