CODEWITHSUNDEEP
triggersinformix
Jimmy Jose
Jimmy Jose

Reputation: 41

How do you identify the triggers associated with a table in a informix database?

How can we identify the triggers associated with a table in a Informix database?

Upvotes: 4

Views: 7101

Answers (4)

Jonathan Leffler
Jonathan Leffler

Reputation: 754450

The information is in the SysTriggers and SysTrigBody system catalog tables — primarily SysTriggers. You can find the description of these (and all other system catalog tables) in the Informix Guide to SQL: Reference manual. You can find that at the Informix 11.70 Info Centre. In particular, the tabid (from SysTables) identifies the triggers for a table in SysTriggers.

Upvotes: 2

BlueMystic
BlueMystic

Reputation: 2297

--OBTIENE LOS TRIGGERS DE LA TABLA:
SELECT T.tabid, TRIM(T.owner) owner, T.tabname, TR.trigid, TRIM(TR.owner) tr_owner, TR.trigname, TR.event, TR.old, TR.new, TR.mode, TRIM(TR.collation) collation,
TB.datakey, TB.seqno, TB.data
FROM systables T, systriggers TR, systrigbody TB
WHERE T.tabname = 'table_name' AND TR.tabid = T.tabid AND TB.trigid = TR.trigid AND TB.datakey IN ('D', 'A')
ORDER BY TB.trigid, TB.datakey DESC, TB.seqno ASC;

--Event: type of triggering event: D = Delete trigger, I = Insert trigger ,U = Update trigger ,S = Select trigger , d = INSTEAD OF Delete trigger , i = INSTEAD OF Insert trigger ,u = INSTEAD OF Update trigger (IDS)

--Old: Name of value before update.

--New: Name of value after update.

--DataKey: Code specifying the type of data: A = ASCII text for the body, triggered actions, B = Linearized code for the body, D = English text for the header, trigger definition, H = Linearized code for the header, S = Linearized code for the symbol table.

Upvotes: 1

Dileep
Dileep

Reputation: 775

 select tabname,a.* from systriggers a, systables b
where a.tabid=b.tabid and tabname="TableName"

or 

dbschema -d db -t tablename

Upvotes: 2

Michał Niklas
Michał Niklas

Reputation: 54322

As Jonathan answered you can use systriggers and other system catalog tables. I used them in my schema reporting utility: http://code.activestate.com/recipes/576621-dump-informix-schema-to-text/

This utility can work with Python and ODBC or with Jython and JDBC. It shows info about trigger for every table like:

--- triggers ---
defbookacc  defbookacc_dtrg D
defbookacc  defbookacc_itrg I
defbookacc  defbookacc_utrg U
mc_loadman  loadman_del D

and then shows body for each trigger.

Upvotes: 1

Related Questions