dbmcp_postgres/tools/
list_triggers.rs

1//! MCP tool: `listTriggers`.
2
3use std::borrow::Cow;
4
5use dbmcp_server::pagination::Pager;
6use dbmcp_sql::Connection as _;
7use rmcp::handler::server::router::tool::{AsyncTool, ToolBase};
8use rmcp::model::{ErrorData, ToolAnnotations};
9
10use crate::PostgresHandler;
11use crate::types::{ListTriggersRequest, ListTriggersResponse};
12
13/// Marker type for the `listTriggers` MCP tool.
14pub(crate) struct ListTriggersTool;
15
16impl ListTriggersTool {
17    const NAME: &'static str = "listTriggers";
18    const TITLE: &'static str = "List Triggers";
19    const DESCRIPTION: &'static str = r#"List user-defined triggers in the `public` schema, optionally filtered and/or with full metadata.
20
21<usecase>
22Use when:
23- Auditing triggers across a database (brief mode, default).
24- Searching for a trigger by partial name (pass `search`).
25- Inspecting a trigger's timing, events, activation level, handler function, status, and full `CREATE TRIGGER` text before reasoning about side-effects (pass `detailed: true`). Detailed mode supersedes ad-hoc `readQuery` against `pg_trigger` / `information_schema.triggers`.
26</usecase>
27
28<parameters>
29- `database` — Database to target. Defaults to the active database.
30- `cursor` — Opaque pagination cursor; echo the prior response's `nextCursor`.
31- `search` — Case-insensitive filter on trigger names via `ILIKE`. `%` matches any sequence; `_` matches a single character.
32- `detailed` — When `true`, returns full metadata objects keyed by trigger name instead of bare name strings. Default `false`.
33</parameters>
34
35<examples>
36✓ "What triggers are in the mydb database?" → listTriggers(database="mydb")
37✓ "Find the audit triggers" → listTriggers(search="audit")
38✓ "What does orders_audit_after_iu do?" → listTriggers(search="orders_audit_after_iu", detailed=true)
39✗ "Show me a trigger's body" → use detailed mode; the `definition` field carries the full `CREATE TRIGGER` text
40</examples>
41
42<what_it_returns>
43Brief mode (default): a sorted JSON array of trigger-name strings, e.g. `["customers_audit_after_insert", "orders_audit_after_insert"]`.
44Detailed mode: a JSON object keyed by trigger name; each value carries `schema`, `table`, `status` (ENABLED/DISABLED/REPLICA/ALWAYS), `timing` (BEFORE/AFTER/INSTEAD OF), `events` (array of strings drawn from INSERT/UPDATE/DELETE/TRUNCATE in that fixed order), `activationLevel` (ROW/STATEMENT), `functionName`, and `definition` (the full `CREATE TRIGGER` text). Internal triggers (FK enforcement etc.) are excluded.
45</what_it_returns>
46
47<pagination>
48Paginated. Pass the prior response's `nextCursor` as `cursor` to fetch the next page. The `search` filter must stay the same across pages for cursor continuity.
49</pagination>"#;
50}
51
52impl ToolBase for ListTriggersTool {
53    type Parameter = ListTriggersRequest;
54    type Output = ListTriggersResponse;
55    type Error = ErrorData;
56
57    fn name() -> Cow<'static, str> {
58        Self::NAME.into()
59    }
60
61    fn title() -> Option<String> {
62        Some(Self::TITLE.into())
63    }
64
65    fn description() -> Option<Cow<'static, str>> {
66        Some(Self::DESCRIPTION.into())
67    }
68
69    fn annotations() -> Option<ToolAnnotations> {
70        Some(
71            ToolAnnotations::new()
72                .read_only(true)
73                .destructive(false)
74                .idempotent(true)
75                .open_world(false),
76        )
77    }
78}
79
80impl AsyncTool<PostgresHandler> for ListTriggersTool {
81    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
82        handler.list_triggers(params).await
83    }
84}
85
86/// Brief-mode SQL: `pg_trigger` join with optional `ILIKE` filter on trigger name.
87///
88/// `'public'::regnamespace` casts the schema name to a `pg_namespace.oid`
89/// at plan time, replacing what would otherwise be a `pg_namespace` join.
90/// Trigger names are not globally unique on Postgres — the same trigger
91/// name can appear on multiple relations — so `c.relname` is the secondary
92/// sort key, matching the detailed-mode `ORDER BY` and keeping `OFFSET`
93/// pagination stable across duplicate names.
94const BRIEF_SQL: &str = r"
95    SELECT t.tgname
96    FROM pg_trigger t
97    JOIN pg_class c ON t.tgrelid = c.oid
98    WHERE c.relnamespace = 'public'::regnamespace
99      AND NOT t.tgisinternal
100      AND ($1::text IS NULL OR t.tgname ILIKE '%' || $1 || '%')
101    ORDER BY t.tgname, c.relname
102    LIMIT $2 OFFSET $3";
103
104/// Detailed-mode SQL: per-trigger `json_build_object` projection.
105///
106/// `'public'::regnamespace` casts the schema name to a `pg_namespace.oid`
107/// at plan time, eliminating the otherwise-needed `pg_namespace` join.
108/// `schema` is hard-coded `'public'` in the output since the WHERE filter
109/// already pins it to that one namespace. Postgres defers SELECT-list
110/// evaluation past `LIMIT`, so `pg_get_triggerdef` and the events array
111/// only run for the page's rows.
112///
113/// `tgtype` bitmask values are stable since 8.4 and defined in
114/// `src/include/catalog/pg_trigger.h`:
115/// `TRIGGER_TYPE_ROW`=1, `TRIGGER_TYPE_BEFORE`=2, `TRIGGER_TYPE_INSERT`=4,
116/// `TRIGGER_TYPE_DELETE`=8, `TRIGGER_TYPE_UPDATE`=16,
117/// `TRIGGER_TYPE_TRUNCATE`=32, `TRIGGER_TYPE_INSTEAD`=64.
118const DETAILED_SQL: &str = r"
119    SELECT
120        t.tgname AS name,
121        json_build_object(
122            'schema',          'public',
123            'table',           c.relname,
124            'status',          CASE t.tgenabled
125                                   WHEN 'O' THEN 'ENABLED'
126                                   WHEN 'D' THEN 'DISABLED'
127                                   WHEN 'R' THEN 'REPLICA'
128                                   WHEN 'A' THEN 'ALWAYS'
129                               END,
130            'timing',          CASE
131                                   WHEN (t.tgtype & 2)  = 2  THEN 'BEFORE'
132                                   WHEN (t.tgtype & 64) = 64 THEN 'INSTEAD OF'
133                                   ELSE 'AFTER'
134                               END,
135            'events',          to_json(array_remove(ARRAY[
136                                   CASE WHEN (t.tgtype & 4)  = 4  THEN 'INSERT'   END,
137                                   CASE WHEN (t.tgtype & 16) = 16 THEN 'UPDATE'   END,
138                                   CASE WHEN (t.tgtype & 8)  = 8  THEN 'DELETE'   END,
139                                   CASE WHEN (t.tgtype & 32) = 32 THEN 'TRUNCATE' END
140                               ], NULL)),
141            'activationLevel', CASE WHEN (t.tgtype & 1) = 1 THEN 'ROW' ELSE 'STATEMENT' END,
142            'functionName',    p.proname,
143            'definition',      pg_get_triggerdef(t.oid)
144        ) AS entry
145    FROM pg_trigger t
146    JOIN pg_class c ON t.tgrelid = c.oid
147    LEFT JOIN pg_proc p ON p.oid = t.tgfoid
148    WHERE c.relnamespace = 'public'::regnamespace
149      AND NOT t.tgisinternal
150      AND ($1::text IS NULL OR t.tgname ILIKE '%' || $1 || '%')
151    ORDER BY t.tgname, c.relname
152    LIMIT $2 OFFSET $3";
153
154impl PostgresHandler {
155    /// Lists one page of user-defined triggers, optionally filtered and/or detailed.
156    ///
157    /// # Errors
158    ///
159    /// Returns [`ErrorData`] with code `-32602` if `cursor` is malformed,
160    /// or an internal-error [`ErrorData`] if `database` is invalid
161    /// or the underlying query fails.
162    pub async fn list_triggers(
163        &self,
164        ListTriggersRequest {
165            database,
166            cursor,
167            search,
168            detailed,
169        }: ListTriggersRequest,
170    ) -> Result<ListTriggersResponse, ErrorData> {
171        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
172        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
173        let pager = Pager::new(cursor, self.config.page_size);
174
175        if detailed {
176            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
177                .connection
178                .fetch(
179                    sqlx::query(DETAILED_SQL)
180                        .bind(pattern)
181                        .bind(pager.limit())
182                        .bind(pager.offset()),
183                    database,
184                )
185                .await?;
186            let (rows, next_cursor) = pager.paginate(rows);
187            return Ok(ListTriggersResponse::detailed(
188                rows.into_iter().map(|(name, json)| (name, json.0)).collect(),
189                next_cursor,
190            ));
191        }
192
193        let rows: Vec<String> = self
194            .connection
195            .fetch_scalar(
196                sqlx::query(BRIEF_SQL)
197                    .bind(pattern)
198                    .bind(pager.limit())
199                    .bind(pager.offset()),
200                database,
201            )
202            .await?;
203        let (triggers, next_cursor) = pager.paginate(rows);
204        Ok(ListTriggersResponse::brief(triggers, next_cursor))
205    }
206}
dbmcp_postgres/tools/list_triggers.rs

dbmcp_postgres/tools/
list_triggers.rs
