Skip to main content

schema_sql_generator/sqlserver/
sqlserver_trigger_generator.rs

1use crate::common::generator_context::GeneratorContext;
2use crate::common::sql_string::escape_sql_literal;
3use crate::common::trigger_generator::TriggerGenerator;
4use crate::sql_println;
5use schema_model::model::table::Table;
6use schema_model::model::types::{DatabaseType, ForeignKeyMode, RelationType, TriggerType};
7
8pub struct SqlServerTriggerGenerator {
9    context: GeneratorContext,
10}
11
12impl SqlServerTriggerGenerator {
13    pub fn new(context: GeneratorContext) -> Self {
14        Self { context }
15    }
16
17    fn should_output_delete_trigger(&self, table: &Table) -> bool {
18        let has_delete_triggers = table
19            .triggers()
20            .iter()
21            .any(|t| t.trigger_type() == TriggerType::Delete);
22
23        let has_reverse_relations_with_triggers = !table.reverse_relations().is_empty()
24            && self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers;
25
26        let has_aggregations = !table.aggregations().is_empty();
27
28        has_delete_triggers || has_reverse_relations_with_triggers || has_aggregations
29    }
30
31    fn should_output_update_trigger(&self, table: &Table) -> bool {
32        let has_update_triggers = table
33            .triggers()
34            .iter()
35            .any(|t| t.trigger_type() == TriggerType::Update);
36
37        let has_relations_with_triggers = !table.relations().is_empty()
38            && self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers;
39
40        let has_aggregations = !table.aggregations().is_empty();
41
42        has_update_triggers || has_relations_with_triggers || has_aggregations
43    }
44
45}
46
47impl TriggerGenerator for SqlServerTriggerGenerator {
48    fn output_triggers(&self) {
49        let database_model = self.context.settings().database_model();
50
51        for table in database_model.all_tables() {
52            self.output_triggers_for_table(table);
53        }
54    }
55
56    fn output_triggers_for_table(&self, table: &Table) {
57        let separator = self.context.settings().statement_separator();
58
59        if self.should_output_delete_trigger(table) && table.primary_key().is_some() {
60            self.output_delete_trigger(table, separator);
61        }
62
63        if self.should_output_update_trigger(table) {
64            self.output_update_trigger(table, separator);
65        }
66    }
67}
68
69impl SqlServerTriggerGenerator {
70    fn output_delete_trigger(&self, table: &Table, separator: &str) {
71        let database_type = self.context.settings().database_type();
72        let table_name = table.name().to_lowercase();
73        let fully_qualified_table = table.fully_qualified_table_name(database_type);
74        let fully_qualified_trigger = database_type.qualified_name(table.schema_name(), &format!("{}_delete", table_name));
75
76        self.context.with_writer(|writer| {
77            sql_println!(writer, "/* {}_delete */", table_name);
78            sql_println!(
79                writer,
80                "if object_id('{}', 'TR') is not null",
81                escape_sql_literal(&fully_qualified_trigger)
82            );
83            sql_println!(writer, "   drop trigger {}{}", fully_qualified_trigger, separator);
84            sql_println!(writer, "");
85            sql_println!(writer, "create trigger {}_delete on {} for delete as", table_name, fully_qualified_table);
86            sql_println!(writer, "if (select count(*) from deleted) > 0");
87            sql_println!(writer, "BEGIN");
88
89            // A reverse relation carries the *original* relation's fields unchanged
90            // (attached to the parent for convenient lookup): `from_table_name`/
91            // `from_column_name` identify the child table and its FK column, while
92            // `to_table_name`/`to_column_name` still refer to this table (the parent)
93            // itself. The child table must be resolved via `from_table_name`, not
94            // `to_table_name` - using `to_table_name` here would resolve back to this
95            // same table and generate a trigger that deletes/updates/checks itself.
96            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
97                let mut first_enforce = true;
98                for relation in table.reverse_relations() {
99                    if matches!(relation.relation_type(), RelationType::Enforce) {
100                        if first_enforce {
101                            sql_println!(writer, "   declare @msg varchar(2000)");
102                            first_enforce = false;
103                        }
104                        let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
105                        sql_println!(
106                            writer,
107                            "   if (select count(*) from {} where {} in (select {} from deleted)) > 0",
108                            child_table.fully_qualified_table_name(database_type),
109                            relation.from_column_name(),
110                            relation.to_column_name()
111                        );
112                        sql_println!(writer, "   begin");
113                        sql_println!(
114                            writer,
115                            "      select @msg = 'The {} ' + (select top 1 convert(varchar, {}) from deleted where {} in (select {} from {})) + ' cannot be deleted. It is being used by a row in the {} table.'",
116                            fully_qualified_table,
117                            relation.to_column_name(),
118                            relation.to_column_name(),
119                            relation.from_column_name(),
120                            child_table.fully_qualified_table_name(database_type),
121                            child_table.fully_qualified_table_name(database_type)
122                        );
123                        sql_println!(writer, "      rollback transaction");
124                        sql_println!(writer, "      raiserror (@msg, 16, 1)");
125                        sql_println!(writer, "      return");
126                        sql_println!(writer, "   end;");
127                    }
128                }
129
130                for relation in table.reverse_relations() {
131                    if matches!(relation.relation_type(), RelationType::SetNull) {
132                        let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
133                        sql_println!(
134                            writer,
135                            "   update {} set {} = null where {} in (select {} from deleted);",
136                            child_table.fully_qualified_table_name(database_type),
137                            relation.from_column_name(),
138                            relation.from_column_name(),
139                            relation.to_column_name()
140                        );
141                    }
142                }
143
144                for relation in table.reverse_relations() {
145                    if matches!(relation.relation_type(), RelationType::Cascade) {
146                        let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
147                        sql_println!(
148                            writer,
149                            "   delete from {} where {} in (select {} from deleted);",
150                            child_table.fully_qualified_table_name(database_type),
151                            relation.from_column_name(),
152                            relation.to_column_name()
153                        );
154                    }
155                }
156            }
157
158            for custom_trigger in table.triggers() {
159                if custom_trigger.trigger_type() == TriggerType::Delete
160                    && custom_trigger.database_type() == DatabaseType::SqlServer
161                {
162                    sql_println!(writer, "{}", custom_trigger.trigger_text());
163                }
164            }
165
166            sql_println!(writer, "END{}", separator);
167            sql_println!(writer, "");
168        });
169    }
170
171    fn output_update_trigger(&self, table: &Table, separator: &str) {
172        let database_type = self.context.settings().database_type();
173        let table_name = table.name().to_lowercase();
174        let fully_qualified_table = table.fully_qualified_table_name(database_type);
175        let fully_qualified_trigger = database_type.qualified_name(table.schema_name(), &format!("{}_update", table_name));
176
177        self.context.with_writer(|writer| {
178            sql_println!(writer, "/* {}_update */", table_name);
179            sql_println!(
180                writer,
181                "if object_id('{}', 'TR') is not null",
182                escape_sql_literal(&fully_qualified_trigger)
183            );
184            sql_println!(writer, "   drop trigger {}{}", fully_qualified_trigger, separator);
185            sql_println!(writer, "");
186            sql_println!(writer, "create trigger {}_update on {} for insert, update as", table_name, fully_qualified_table);
187            sql_println!(writer, "if (select count(*) from inserted) > 0");
188            sql_println!(writer, "BEGIN");
189
190            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
191                for relation in table.relations() {
192                    match relation.relation_type() {
193                        RelationType::Enforce | RelationType::SetNull | RelationType::Cascade => {
194                            let to_table = self.database_model().find_table_by_qualified_name(relation.to_table_name());
195                            sql_println!(
196                                writer,
197                                "   if (select count(*) from inserted where {} is not null and {} not in (select {} from {})) > 0",
198                                relation.from_column_name(),
199                                relation.from_column_name(),
200                                relation.to_column_name(),
201                                to_table.fully_qualified_table_name(database_type)
202                            );
203                            sql_println!(writer, "   begin");
204                            sql_println!(
205                                writer,
206                                "      raiserror ('The value of {} was not found in the {} table.', 16, 1)",
207                                relation.from_column_name(),
208                                to_table.fully_qualified_table_name(database_type)
209                            );
210                            sql_println!(writer, "      rollback transaction");
211                            sql_println!(writer, "      return");
212                            sql_println!(writer, "   end;");
213                        }
214                        RelationType::DoNothing => {}
215                    }
216                }
217            }
218
219            for custom_trigger in table.triggers() {
220                if custom_trigger.trigger_type() == TriggerType::Update
221                    && custom_trigger.database_type() == DatabaseType::SqlServer
222                {
223                    sql_println!(writer, "{}", custom_trigger.trigger_text());
224                }
225            }
226
227            sql_println!(writer, "END{}", separator);
228            sql_println!(writer, "");
229        });
230    }
231
232    fn database_model(&self) -> &schema_model::model::database_model::DatabaseModel {
233        self.context.settings().database_model()
234    }
235}
236
237#[cfg(test)]
238mod tests {
239    use super::*;
240    use crate::common::test_support::make_context_with_fk_mode;
241    use schema_model::builder::{ColumnBuilder, SchemaBuilder, TableBuilder};
242    use schema_model::model::column_type::ColumnType;
243    use schema_model::model::database_model::DatabaseModel;
244    use schema_model::model::relation::Relation;
245    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode, RelationType};
246
247    fn build_model_with_relation() -> DatabaseModel {
248        // Schema-qualify both the tables and the relation's to_table_name: find_table() here
249        // does relation.to_table_name().split('.').next() to get a schema name, which only
250        // works for qualified references (mirrors the same quirk in PostgresTriggerGenerator).
251        let parent = TableBuilder::new(Some("app"), "parent")
252            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
253            .build();
254        let child = TableBuilder::new(Some("app"), "child")
255            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
256            .add_relation(Relation::new("app.parent", "id", "child", "parent_id", RelationType::Enforce, false))
257            .build();
258        let schema = SchemaBuilder::new(Some("app"))
259            .add_table(parent)
260            .add_table(child)
261            .build();
262        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
263    }
264
265    #[test]
266    fn output_triggers_renders_update_validation_trigger_for_relations_mode_triggers() {
267        let model = build_model_with_relation();
268        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
269
270        let generator = SqlServerTriggerGenerator::new(ctx);
271        generator.output_triggers();
272
273        let output = buffer.contents();
274        assert!(output.contains("create trigger child_update on app.child for insert, update as"));
275        assert!(output.contains("was not found in the app.parent table"));
276        assert!(output.contains("raiserror"));
277        assert!(output.contains("rollback transaction"));
278    }
279
280    #[test]
281    fn output_triggers_qualifies_drop_trigger_with_schema() {
282        let model = build_model_with_relation();
283        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
284
285        let generator = SqlServerTriggerGenerator::new(ctx);
286        generator.output_triggers();
287
288        let output = buffer.contents();
289        assert!(output.contains("drop trigger app.child_update"));
290    }
291
292    #[test]
293    fn output_triggers_qualifies_drop_trigger_with_default_schema_when_none() {
294        let table = TableBuilder::new(None::<&str>, "widget")
295            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
296            .add_trigger(schema_model::model::trigger::Trigger::new(
297                "-- custom",
298                schema_model::model::types::TriggerType::Update,
299                DatabaseType::SqlServer,
300            ))
301            .build();
302        let schema = SchemaBuilder::new(None::<&str>).add_table(table).build();
303        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
304        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Relations);
305
306        let generator = SqlServerTriggerGenerator::new(ctx);
307        generator.output_triggers();
308
309        let output = buffer.contents();
310        assert!(output.contains("drop trigger dbo.widget_update"));
311    }
312
313    #[test]
314    fn output_triggers_escapes_single_quote_in_table_name() {
315        // Regression test: an unescaped embedded quote would break the generated
316        // object_id existence-check SQL string literal.
317        let table = TableBuilder::new(None::<&str>, "o'brien")
318            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
319            .add_trigger(schema_model::model::trigger::Trigger::new(
320                "-- custom",
321                schema_model::model::types::TriggerType::Update,
322                DatabaseType::SqlServer,
323            ))
324            .build();
325        let schema = SchemaBuilder::new(None::<&str>).add_table(table).build();
326        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
327        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Relations);
328
329        let generator = SqlServerTriggerGenerator::new(ctx);
330        generator.output_triggers();
331
332        let output = buffer.contents();
333        assert!(output.contains("if object_id('dbo.o''brien_update', 'TR') is not null"));
334    }
335
336    #[test]
337    fn output_triggers_does_nothing_when_relations_mode_is_not_triggers() {
338        let model = build_model_with_relation();
339        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Relations);
340
341        let generator = SqlServerTriggerGenerator::new(ctx);
342        generator.output_triggers();
343
344        assert_eq!(buffer.contents(), "");
345    }
346
347    /// Builds parent/child tables with both the forward relation (on `child`, used for the
348    /// update trigger) and the matching reverse relation attached to `parent` (used for the
349    /// delete trigger) - mirroring what `convert::reverse_relations()` does for a real parsed
350    /// schema, since these unit tests build the model directly via builders rather than XML.
351    fn build_model_with_reverse_relation(relation_type: RelationType) -> DatabaseModel {
352        let mut parent = TableBuilder::new(Some("app"), "parent")
353            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
354            .add_key(schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary).add_column("id").build())
355            .build();
356        parent.add_reverse_relation(Relation::new("app.parent", "id", "app.child", "parent_id", relation_type, false));
357
358        let child = TableBuilder::new(Some("app"), "child")
359            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
360            .add_relation(Relation::new("app.parent", "id", "child", "parent_id", relation_type, false))
361            .build();
362        let schema = SchemaBuilder::new(Some("app"))
363            .add_table(parent)
364            .add_table(child)
365            .build();
366        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
367    }
368
369    #[test]
370    fn output_delete_trigger_enforce_checks_the_child_table_not_itself() {
371        let model = build_model_with_reverse_relation(RelationType::Enforce);
372        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
373
374        let generator = SqlServerTriggerGenerator::new(ctx);
375        generator.output_triggers();
376
377        let output = buffer.contents();
378        assert!(output.contains("create trigger parent_delete on app.parent for delete as"));
379        assert!(output.contains("if (select count(*) from app.child where parent_id in (select id from deleted)) > 0"));
380        assert!(output.contains("cannot be deleted. It is being used by a row in the app.child table"));
381    }
382
383    #[test]
384    fn output_delete_trigger_setnull_updates_the_child_table_not_itself() {
385        let model = build_model_with_reverse_relation(RelationType::SetNull);
386        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
387
388        let generator = SqlServerTriggerGenerator::new(ctx);
389        generator.output_triggers();
390
391        let output = buffer.contents();
392        assert!(output.contains("update app.child set parent_id = null where parent_id in (select id from deleted);"));
393    }
394
395    #[test]
396    fn output_delete_trigger_cascade_deletes_from_the_child_table_not_itself() {
397        let model = build_model_with_reverse_relation(RelationType::Cascade);
398        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
399
400        let generator = SqlServerTriggerGenerator::new(ctx);
401        generator.output_triggers();
402
403        let output = buffer.contents();
404        assert!(output.contains("delete from app.child where parent_id in (select id from deleted);"));
405    }
406
407    #[test]
408    fn output_delete_trigger_uses_the_relations_own_column_for_a_composite_primary_key() {
409        // The parent has a composite primary key (id, tenant_id); the relation specifically
410        // references `id`. The delete trigger must match on the relation's own referenced
411        // column, not an arbitrarily-picked primary key column.
412        let mut parent = TableBuilder::new(Some("app"), "parent")
413            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
414            .add_column(ColumnBuilder::new(None::<&str>, "tenant_id", ColumnType::Int).required(true).build())
415            .add_key(
416                schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary)
417                    .add_column("id")
418                    .add_column("tenant_id")
419                    .build(),
420            )
421            .build();
422        parent.add_reverse_relation(Relation::new("app.parent", "id", "app.child", "parent_id", RelationType::Cascade, false));
423
424        let child = TableBuilder::new(Some("app"), "child")
425            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
426            .build();
427        let schema = SchemaBuilder::new(Some("app"))
428            .add_table(parent)
429            .add_table(child)
430            .build();
431        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
432        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
433
434        let generator = SqlServerTriggerGenerator::new(ctx);
435        generator.output_triggers();
436
437        let output = buffer.contents();
438        assert!(output.contains("delete from app.child where parent_id in (select id from deleted);"));
439    }
440}