ODBC API: How to get list of database triggers?

Is there a way to retrieve a list of all database triggers using the
ODBC API?
I'm looking for something like SQLProcedures that can retrieve a list
of all database procedures.
Are triggers not supported by the ODBC catalog functions?
Why not'
I CANNOT use any database specific software such as the MS SQLServer
DMO object. Only the ODBC API functions."aRIEL" wrote...

> Is there a way to retrieve a list of all database
> triggers using the ODBC API?
> I'm looking for something like SQLProcedures that
> can retrieve a list of all database procedures.
> Are triggers not supported by the ODBC catalog functions?
> Why not'
In some cases you'll probably can't get anything comprehensible from it, as
not *all* databases support triggers.

> I CANNOT use any database specific software such
> as the MS SQLServer DMO object. Only the ODBC API functions.
Why? From a VB perspective you'd probably be better off using some OLE DB
provider, or something other possible to use in the concept of ADO. From
Java Perspective you'd be better off using thin JDBC-drivers instead of a
JDBC/ODBC-bridge...
Anyway...
Your actual question does not have much to do with some of the newsgroups
you've posted to.
Anyway, as one of the groups you've posted to is comp.lang.java.databases,
and I read your question there, I'll give you an answer from the Java
perspective.
I think what you search for could be accomplished in Java with the following
quick example:
// Open a connection to the database
Connection conn = DriverManager.getConnection(url);
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet proc = dbmd.getProcedures(null, null, "%");
// Printout data on stored procedures
while(proc.next())
{
String dbObjectCatalog = proc.getString(1);
String dbObjectSchema = proc.getString(2);
String dbObjectName = proc.getString(3);
String dbObjectRemarks = proc.getString(7);
String dbObjectType = proc.getString(8); // really a short, read the
docs
System.out.println("" + dbObjectType + ": " + dbObjectName);
System.out.println(" Catalog: " + dbObjectCatalog);
System.out.println(" Schema: " + dbObjectSchema);
System.out.println(" Remarks: " + dbObjectRemarks);
}
Note that you'll get all procedures, not only triggers, but that shouldn't
be so hard to figure out through the "type".
// Bjorn A

ODBC API: How to get list of database triggers?

Is there a way to retrieve a list of all database triggers using the
ODBC API?
I'm looking for something like SQLProcedures that can retrieve a list
of all database procedures.
Are triggers not supported by the ODBC catalog functions?
Why not?
I CANNOT use any database specific software such as the MS SQLServer
DMO object. Only the ODBC API functions.
"aRIEL" wrote...

> Is there a way to retrieve a list of all database
> triggers using the ODBC API?
> I'm looking for something like SQLProcedures that
> can retrieve a list of all database procedures.
> Are triggers not supported by the ODBC catalog functions?
> Why not?
In some cases you'll probably can't get anything comprehensible from it, as
not *all* databases support triggers.

> I CANNOT use any database specific software such
> as the MS SQLServer DMO object. Only the ODBC API functions.
Why? From a VB perspective you'd probably be better off using some OLE DB
provider, or something other possible to use in the concept of ADO. From
Java Perspective you'd be better off using thin JDBC-drivers instead of a
JDBC/ODBC-bridge...
Anyway...
Your actual question does not have much to do with some of the newsgroups
you've posted to.
Anyway, as one of the groups you've posted to is comp.lang.java.databases,
and I read your question there, I'll give you an answer from the Java
perspective.
I think what you search for could be accomplished in Java with the following
quick example:
// Open a connection to the database
Connection conn = DriverManager.getConnection(url);
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet proc = dbmd.getProcedures(null, null, "%");
// Printout data on stored procedures
while(proc.next())
{
String dbObjectCatalog = proc.getString(1);
String dbObjectSchema = proc.getString(2);
String dbObjectName = proc.getString(3);
String dbObjectRemarks = proc.getString(7);
String dbObjectType = proc.getString(8); // really a short, read the
docs
System.out.println("" + dbObjectType + ": " + dbObjectName);
System.out.println(" Catalog: " + dbObjectCatalog);
System.out.println(" Schema: " + dbObjectSchema);
System.out.println(" Remarks: " + dbObjectRemarks);
}
Note that you'll get all procedures, not only triggers, but that shouldn't
be so hard to figure out through the "type".
// Bjorn A

obtaining the names of triggers within T-SQL

I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
trigger that updates both tables. I want to prevent inserts on the base
tables themselves with an INSTEAD OF INSERT trigger that would raise an erro
r
indicating that the appliation should insert against the view and then
rolling back the insert on the base table.
Is there a way to find out the names of the triggers in play so I could do
this? (I've experimented with trigger_nestlevel() but it doesn't do exactly
what I want.)
This is on SQL Server 2000.Brian,
Deny "insert" permission on the table, grant "insert" on the view.
AMB
"Brian Cobb" wrote:

> I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
> trigger that updates both tables. I want to prevent inserts on the base
> tables themselves with an INSTEAD OF INSERT trigger that would raise an er
ror
> indicating that the appliation should insert against the view and then
> rolling back the insert on the base table.
> Is there a way to find out the names of the triggers in play so I could do
> this? (I've experimented with trigger_nestlevel() but it doesn't do exact
ly
> what I want.)
> This is on SQL Server 2000.
>|||That will work only if one can safely assume one has control over what users
and roles will be permitted in the database, won't it?
"Alejandro Mesa" wrote:
> Brian,
> Deny "insert" permission on the table, grant "insert" on the view.
>
> AMB
> "Brian Cobb" wrote:
>|||> That will work only if one can safely assume one has control over what
> users
> and roles will be permitted in the database, won't it?
Yes - And why wouldn't one make this assumption? Any "solution" can be
circumvented by someone with administrative access to the database. The
suggestion, by far, is the most straightforward one and involves no obscure
code to determine the "triggers in play" (without know specifically what
that means).|||I thought "triggers in play" was obvious given the context in which it was
used in my original post. But let me try to clarify. If a trigger,
trigger1, performs an action that initiates another trigger, trigger2, is it
possible within trigger2 to determine the name of the initiating trigger?
I am only interested in an answer to my question as posed in the original
post, and (hopefully) clarified here. Please feel free not to respond if yo
u
can't answer it.
"Scott Morris" wrote:

> Yes - And why wouldn't one make this assumption? Any "solution" can be
> circumvented by someone with administrative access to the database. The
> suggestion, by far, is the most straightforward one and involves no obscur
e
> code to determine the "triggers in play" (without know specifically what
> that means).
>
>|||On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:

>I thought "triggers in play" was obvious given the context in which it was
>used in my original post. But let me try to clarify. If a trigger,
>trigger1, performs an action that initiates another trigger, trigger2, is i
t
>possible within trigger2 to determine the name of the initiating trigger?
Hi Brian,
There's no easy way to do that. The only method I can think of is to
query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
that might be active when trigger2 runs; if it's not 0, trigger2 must
have been fired (either directly or indirectly) by that trigger.

>I am only interested in an answer to my question as posed in the original
>post, and (hopefully) clarified here. Please feel free not to respond if y
ou
>can't answer it.
The only correct answer to your original post would be to dump the idea
of enforcing this with triggers. Use DCL, as suggested by Alejandro.
Hugo Kornelis, SQL Server MVP|||I suspected it probably couldn't be done the way I hoped but wanted some
outside input. I had already considered something similar to what Mr. Mesa
had suggested, prior to my first post, which I will now revisit.
Thank you for your response.
"Hugo Kornelis" wrote:

> On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:
>
> Hi Brian,
> There's no easy way to do that. The only method I can think of is to
> query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
> that might be active when trigger2 runs; if it's not 0, trigger2 must
> have been fired (either directly or indirectly) by that trigger.
>
> The only correct answer to your original post would be to dump the idea
> of enforcing this with triggers. Use DCL, as suggested by Alejandro.
> --
> Hugo Kornelis, SQL Server MVP
>