List Oracle Triggers from SQL Server 2005 Linked Server

Question

Post reply

List Oracle Triggers from SQL Server 2005 Linked Server

Bitter Monkey

SSC Enthusiast

Points: 172
More actions
March 12, 2010 at 8:45 am

#386492

How do I list all the triggers from:
1 - a specific table
2 - the entire database
thru a linked server in sql server 2005?
Linked Server Name: LS20001
Schema Name: PPACT
Thank you in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there are views in Oracle named USER_TRIGGERS or even ALL_TRIGGERS

--specific table

select * from user_triggers WHERE TABLE_NAME = 'TBTITLE'

--owned by a specific user(equivilent to database)

select * from user_triggers WHERE TABLE_OWNER = 'SCOTT'

you'd use your lined server to query those views:

select * from MyOracle...user_triggers WHERE TABLE_NAME = 'TBTITLE'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bitter Monkey SSC Enthusiast Points: 172 More actions · Answer 2

Lowell, first up, thank you for the quick response.

The question now is how come when I issue this query:

---------------------

SELECT * FROM LS20001..PPACT.user_triggers

I get this error? Our admin told me I got owner privileges.

---------------------

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "MSDAORA" for linked server "LS20001" does not contain the table ""PPACT"."user_triggers"". The table either does not exist or the current user does not have permissions on that table.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think you should NOT include the user/schema:

this should work instead, unless you are logging in a sys or system:

SELECT * FROM LS20001...user_triggers

also, Oracle can be picky on case sensitivity;

SELECT * FROM LS20001...USER_TRIGGERS might be better.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bitter Monkey SSC Enthusiast Points: 172 More actions · Answer 4

1. This query:

----------------------

SELECT * FROM LS20001...user_triggers

Returned:

---------------------

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "MSDAORA" for linked server "LS20001" does not contain the table "user_triggers". The table either does not exist or the current user does not have permissions on that table.

2. While this query:

----------------------

SELECT * FROM LS20001...USER_TRIGGERS

Returned:

----------------------

Msg 7318, Level 16, State 1, Line 1

The OLE DB provider "MSDAORA" for linked server "LS20001" returned an invalid column definition for table "USER_TRIGGERS".