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::relation::Relation;
6use schema_model::model::table::Table;
7use schema_model::model::types::{DatabaseType, ForeignKeyMode, RelationType, TriggerType};
8
9fn join_condition(relation: &Relation, left_alias: &str, right_alias: &str) -> String {
10    relation
11        .column_pairs()
12        .iter()
13        .map(|(from_col, to_col)| format!("{}.{} = {}.{}", left_alias, from_col, right_alias, to_col))
14        .collect::<Vec<_>>()
15        .join(" and ")
16}
17
18pub struct SqlServerTriggerGenerator {
19    context: GeneratorContext,
20}
21
22impl SqlServerTriggerGenerator {
23    pub fn new(context: GeneratorContext) -> Self {
24        Self { context }
25    }
26
27    fn should_output_delete_trigger(&self, table: &Table) -> bool {
28        let has_delete_triggers = table
29            .triggers()
30            .iter()
31            .any(|t| t.trigger_type() == TriggerType::Delete);
32
33        let has_reverse_relations_with_triggers = !table.reverse_relations().is_empty()
34            && self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers;
35
36        let has_aggregations = !table.aggregations().is_empty();
37
38        has_delete_triggers || has_reverse_relations_with_triggers || has_aggregations
39    }
40
41    fn should_output_update_trigger(&self, table: &Table) -> bool {
42        let has_update_triggers = table
43            .triggers()
44            .iter()
45            .any(|t| t.trigger_type() == TriggerType::Update);
46
47        let has_relations_with_triggers = !table.relations().is_empty()
48            && self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers;
49
50        let has_aggregations = !table.aggregations().is_empty();
51
52        has_update_triggers || has_relations_with_triggers || has_aggregations
53    }
54
55}
56
57impl TriggerGenerator for SqlServerTriggerGenerator {
58    fn output_triggers(&self) {
59        let database_model = self.context.settings().database_model();
60
61        for table in database_model.all_tables() {
62            self.output_triggers_for_table(table);
63        }
64    }
65
66    fn output_triggers_for_table(&self, table: &Table) {
67        let separator = self.context.settings().statement_separator();
68
69        if self.should_output_delete_trigger(table) && table.primary_key().is_some() {
70            self.output_delete_trigger(table, separator);
71        }
72
73        if self.should_output_update_trigger(table) {
74            self.output_update_trigger(table, separator);
75        }
76    }
77}
78
79impl SqlServerTriggerGenerator {
80    fn output_delete_trigger(&self, table: &Table, separator: &str) {
81        let database_type = self.context.settings().database_type();
82        let table_name = table.name().to_lowercase();
83        let fully_qualified_table = table.fully_qualified_table_name(database_type);
84        let fully_qualified_trigger = database_type.qualified_name(table.schema_name(), &format!("{}_delete", table_name));
85
86        self.context.with_writer(|writer| {
87            sql_println!(writer, "/* {}_delete */", table_name);
88            sql_println!(
89                writer,
90                "if object_id('{}', 'TR') is not null",
91                escape_sql_literal(&fully_qualified_trigger)
92            );
93            sql_println!(writer, "   drop trigger {}{}", fully_qualified_trigger, separator);
94            sql_println!(writer, "");
95            sql_println!(writer, "create trigger {}_delete on {} for delete as", table_name, fully_qualified_table);
96            sql_println!(writer, "if (select count(*) from deleted) > 0");
97            sql_println!(writer, "BEGIN");
98
99            // A reverse relation carries the *original* relation's fields unchanged
100            // (attached to the parent for convenient lookup): `from_table_name`/
101            // `from_column_name` identify the child table and its FK column, while
102            // `to_table_name`/`to_column_name` still refer to this table (the parent)
103            // itself. The child table must be resolved via `from_table_name`, not
104            // `to_table_name` - using `to_table_name` here would resolve back to this
105            // same table and generate a trigger that deletes/updates/checks itself.
106            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
107                let mut first_enforce = true;
108                for relation in table.reverse_relations() {
109                    if matches!(relation.relation_type(), RelationType::Enforce) {
110                        if first_enforce {
111                            sql_println!(writer, "   declare @msg varchar(2000)");
112                            first_enforce = false;
113                        }
114                        let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
115                        if relation.is_composite() {
116                            sql_println!(
117                                writer,
118                                "   if exists (select 1 from {} c inner join deleted d on {})",
119                                child_table.fully_qualified_table_name(database_type),
120                                join_condition(relation, "c", "d")
121                            );
122                            sql_println!(writer, "   begin");
123                            sql_println!(
124                                writer,
125                                "      select @msg = 'The row in {} cannot be deleted. It is being used by a row in the {} table.'",
126                                fully_qualified_table,
127                                child_table.fully_qualified_table_name(database_type)
128                            );
129                            sql_println!(writer, "      rollback transaction");
130                            sql_println!(writer, "      raiserror (@msg, 16, 1)");
131                            sql_println!(writer, "      return");
132                            sql_println!(writer, "   end;");
133                        } else {
134                            sql_println!(
135                                writer,
136                                "   if (select count(*) from {} where {} in (select {} from deleted)) > 0",
137                                child_table.fully_qualified_table_name(database_type),
138                                relation.from_column_name(),
139                                relation.to_column_name()
140                            );
141                            sql_println!(writer, "   begin");
142                            sql_println!(
143                                writer,
144                                "      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.'",
145                                fully_qualified_table,
146                                relation.to_column_name(),
147                                relation.to_column_name(),
148                                relation.from_column_name(),
149                                child_table.fully_qualified_table_name(database_type),
150                                child_table.fully_qualified_table_name(database_type)
151                            );
152                            sql_println!(writer, "      rollback transaction");
153                            sql_println!(writer, "      raiserror (@msg, 16, 1)");
154                            sql_println!(writer, "      return");
155                            sql_println!(writer, "   end;");
156                        }
157                    }
158                }
159
160                for relation in table.reverse_relations() {
161                    if matches!(relation.relation_type(), RelationType::SetNull) {
162                        let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
163                        if relation.is_composite() {
164                            let set_clause = relation
165                                .column_pairs()
166                                .iter()
167                                .map(|(from_col, _)| format!("{} = null", from_col))
168                                .collect::<Vec<_>>()
169                                .join(", ");
170                            let child = child_table.fully_qualified_table_name(database_type);
171                            sql_println!(
172                                writer,
173                                "   update {} set {} from {} inner join deleted on {};",
174                                child,
175                                set_clause,
176                                child,
177                                join_condition(relation, &child, "deleted")
178                            );
179                        } else {
180                            sql_println!(
181                                writer,
182                                "   update {} set {} = null where {} in (select {} from deleted);",
183                                child_table.fully_qualified_table_name(database_type),
184                                relation.from_column_name(),
185                                relation.from_column_name(),
186                                relation.to_column_name()
187                            );
188                        }
189                    }
190                }
191
192                for relation in table.reverse_relations() {
193                    if matches!(relation.relation_type(), RelationType::Cascade) {
194                        let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
195                        if relation.is_composite() {
196                            let child = child_table.fully_qualified_table_name(database_type);
197                            sql_println!(
198                                writer,
199                                "   delete {} from {} inner join deleted on {};",
200                                child,
201                                child,
202                                join_condition(relation, &child, "deleted")
203                            );
204                        } else {
205                            sql_println!(
206                                writer,
207                                "   delete from {} where {} in (select {} from deleted);",
208                                child_table.fully_qualified_table_name(database_type),
209                                relation.from_column_name(),
210                                relation.to_column_name()
211                            );
212                        }
213                    }
214                }
215            }
216
217            for custom_trigger in table.triggers() {
218                if custom_trigger.trigger_type() == TriggerType::Delete
219                    && custom_trigger.database_type() == DatabaseType::SqlServer
220                {
221                    sql_println!(writer, "{}", custom_trigger.trigger_text());
222                }
223            }
224
225            sql_println!(writer, "END{}", separator);
226            sql_println!(writer, "");
227        });
228    }
229
230    fn output_update_trigger(&self, table: &Table, separator: &str) {
231        let database_type = self.context.settings().database_type();
232        let table_name = table.name().to_lowercase();
233        let fully_qualified_table = table.fully_qualified_table_name(database_type);
234        let fully_qualified_trigger = database_type.qualified_name(table.schema_name(), &format!("{}_update", table_name));
235
236        self.context.with_writer(|writer| {
237            sql_println!(writer, "/* {}_update */", table_name);
238            sql_println!(
239                writer,
240                "if object_id('{}', 'TR') is not null",
241                escape_sql_literal(&fully_qualified_trigger)
242            );
243            sql_println!(writer, "   drop trigger {}{}", fully_qualified_trigger, separator);
244            sql_println!(writer, "");
245            sql_println!(writer, "create trigger {}_update on {} for insert, update as", table_name, fully_qualified_table);
246            sql_println!(writer, "if (select count(*) from inserted) > 0");
247            sql_println!(writer, "BEGIN");
248
249            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
250                for relation in table.relations() {
251                    match relation.relation_type() {
252                        RelationType::Enforce | RelationType::SetNull | RelationType::Cascade => {
253                            let to_table = self.database_model().find_table_by_qualified_name(relation.to_table_name());
254                            if relation.is_composite() {
255                                let not_null_clause = relation
256                                    .column_pairs()
257                                    .iter()
258                                    .map(|(from_col, _)| format!("i.{} is not null", from_col))
259                                    .collect::<Vec<_>>()
260                                    .join(" and ");
261                                let match_clause = relation
262                                    .column_pairs()
263                                    .iter()
264                                    .map(|(from_col, to_col)| format!("p.{} = i.{}", to_col, from_col))
265                                    .collect::<Vec<_>>()
266                                    .join(" and ");
267                                let column_list = relation
268                                    .column_pairs()
269                                    .iter()
270                                    .map(|(from_col, _)| from_col.as_str())
271                                    .collect::<Vec<_>>()
272                                    .join(", ");
273                                sql_println!(
274                                    writer,
275                                    "   if exists (select 1 from inserted i where {} and not exists (select 1 from {} p where {}))",
276                                    not_null_clause,
277                                    to_table.fully_qualified_table_name(database_type),
278                                    match_clause
279                                );
280                                sql_println!(writer, "   begin");
281                                sql_println!(
282                                    writer,
283                                    "      raiserror ('The value of {} was not found in the {} table.', 16, 1)",
284                                    column_list,
285                                    to_table.fully_qualified_table_name(database_type)
286                                );
287                                sql_println!(writer, "      rollback transaction");
288                                sql_println!(writer, "      return");
289                                sql_println!(writer, "   end;");
290                            } else {
291                                sql_println!(
292                                    writer,
293                                    "   if (select count(*) from inserted where {} is not null and {} not in (select {} from {})) > 0",
294                                    relation.from_column_name(),
295                                    relation.from_column_name(),
296                                    relation.to_column_name(),
297                                    to_table.fully_qualified_table_name(database_type)
298                                );
299                                sql_println!(writer, "   begin");
300                                sql_println!(
301                                    writer,
302                                    "      raiserror ('The value of {} was not found in the {} table.', 16, 1)",
303                                    relation.from_column_name(),
304                                    to_table.fully_qualified_table_name(database_type)
305                                );
306                                sql_println!(writer, "      rollback transaction");
307                                sql_println!(writer, "      return");
308                                sql_println!(writer, "   end;");
309                            }
310                        }
311                        RelationType::DoNothing => {}
312                    }
313                }
314            }
315
316            for custom_trigger in table.triggers() {
317                if custom_trigger.trigger_type() == TriggerType::Update
318                    && custom_trigger.database_type() == DatabaseType::SqlServer
319                {
320                    sql_println!(writer, "{}", custom_trigger.trigger_text());
321                }
322            }
323
324            sql_println!(writer, "END{}", separator);
325            sql_println!(writer, "");
326        });
327    }
328
329    fn database_model(&self) -> &schema_model::model::database_model::DatabaseModel {
330        self.context.settings().database_model()
331    }
332}
333
334#[cfg(test)]
335mod tests {
336    use super::*;
337    use crate::common::test_support::make_context_with_fk_mode;
338    use schema_model::builder::{ColumnBuilder, SchemaBuilder, TableBuilder};
339    use schema_model::model::column_type::ColumnType;
340    use schema_model::model::database_model::DatabaseModel;
341    use schema_model::model::relation::Relation;
342    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode, RelationType};
343
344    fn build_model_with_relation() -> DatabaseModel {
345        // Schema-qualify both the tables and the relation's to_table_name: find_table() here
346        // does relation.to_table_name().split('.').next() to get a schema name, which only
347        // works for qualified references (mirrors the same quirk in PostgresTriggerGenerator).
348        let parent = TableBuilder::new(Some("app"), "parent")
349            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
350            .build();
351        let child = TableBuilder::new(Some("app"), "child")
352            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
353            .add_relation(Relation::new("app.parent", "id", "child", "parent_id", RelationType::Enforce, false))
354            .build();
355        let schema = SchemaBuilder::new(Some("app"))
356            .add_table(parent)
357            .add_table(child)
358            .build();
359        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
360    }
361
362    #[test]
363    fn output_triggers_renders_update_validation_trigger_for_relations_mode_triggers() {
364        let model = build_model_with_relation();
365        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
366
367        let generator = SqlServerTriggerGenerator::new(ctx);
368        generator.output_triggers();
369
370        let output = buffer.contents();
371        assert!(output.contains("create trigger child_update on app.child for insert, update as"));
372        assert!(output.contains("was not found in the app.parent table"));
373        assert!(output.contains("raiserror"));
374        assert!(output.contains("rollback transaction"));
375    }
376
377    #[test]
378    fn output_triggers_qualifies_drop_trigger_with_schema() {
379        let model = build_model_with_relation();
380        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
381
382        let generator = SqlServerTriggerGenerator::new(ctx);
383        generator.output_triggers();
384
385        let output = buffer.contents();
386        assert!(output.contains("drop trigger app.child_update"));
387    }
388
389    #[test]
390    fn output_triggers_qualifies_drop_trigger_with_default_schema_when_none() {
391        let table = TableBuilder::new(None::<&str>, "widget")
392            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
393            .add_trigger(schema_model::model::trigger::Trigger::new(
394                "-- custom",
395                schema_model::model::types::TriggerType::Update,
396                DatabaseType::SqlServer,
397            ))
398            .build();
399        let schema = SchemaBuilder::new(None::<&str>).add_table(table).build();
400        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
401        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Relations);
402
403        let generator = SqlServerTriggerGenerator::new(ctx);
404        generator.output_triggers();
405
406        let output = buffer.contents();
407        assert!(output.contains("drop trigger dbo.widget_update"));
408    }
409
410    #[test]
411    fn output_triggers_escapes_single_quote_in_table_name() {
412        // Regression test: an unescaped embedded quote would break the generated
413        // object_id existence-check SQL string literal.
414        let table = TableBuilder::new(None::<&str>, "o'brien")
415            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
416            .add_trigger(schema_model::model::trigger::Trigger::new(
417                "-- custom",
418                schema_model::model::types::TriggerType::Update,
419                DatabaseType::SqlServer,
420            ))
421            .build();
422        let schema = SchemaBuilder::new(None::<&str>).add_table(table).build();
423        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
424        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Relations);
425
426        let generator = SqlServerTriggerGenerator::new(ctx);
427        generator.output_triggers();
428
429        let output = buffer.contents();
430        assert!(output.contains("if object_id('dbo.o''brien_update', 'TR') is not null"));
431    }
432
433    #[test]
434    fn output_triggers_does_nothing_when_relations_mode_is_not_triggers() {
435        let model = build_model_with_relation();
436        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Relations);
437
438        let generator = SqlServerTriggerGenerator::new(ctx);
439        generator.output_triggers();
440
441        assert_eq!(buffer.contents(), "");
442    }
443
444    /// Builds parent/child tables with both the forward relation (on `child`, used for the
445    /// update trigger) and the matching reverse relation attached to `parent` (used for the
446    /// delete trigger) - mirroring what `convert::reverse_relations()` does for a real parsed
447    /// schema, since these unit tests build the model directly via builders rather than XML.
448    fn build_model_with_reverse_relation(relation_type: RelationType) -> DatabaseModel {
449        let mut parent = TableBuilder::new(Some("app"), "parent")
450            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
451            .add_key(schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary).add_column("id").build())
452            .build();
453        parent.add_reverse_relation(Relation::new("app.parent", "id", "app.child", "parent_id", relation_type, false));
454
455        let child = TableBuilder::new(Some("app"), "child")
456            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
457            .add_relation(Relation::new("app.parent", "id", "child", "parent_id", relation_type, false))
458            .build();
459        let schema = SchemaBuilder::new(Some("app"))
460            .add_table(parent)
461            .add_table(child)
462            .build();
463        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
464    }
465
466    #[test]
467    fn output_delete_trigger_enforce_checks_the_child_table_not_itself() {
468        let model = build_model_with_reverse_relation(RelationType::Enforce);
469        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
470
471        let generator = SqlServerTriggerGenerator::new(ctx);
472        generator.output_triggers();
473
474        let output = buffer.contents();
475        assert!(output.contains("create trigger parent_delete on app.parent for delete as"));
476        assert!(output.contains("if (select count(*) from app.child where parent_id in (select id from deleted)) > 0"));
477        assert!(output.contains("cannot be deleted. It is being used by a row in the app.child table"));
478    }
479
480    #[test]
481    fn output_delete_trigger_setnull_updates_the_child_table_not_itself() {
482        let model = build_model_with_reverse_relation(RelationType::SetNull);
483        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
484
485        let generator = SqlServerTriggerGenerator::new(ctx);
486        generator.output_triggers();
487
488        let output = buffer.contents();
489        assert!(output.contains("update app.child set parent_id = null where parent_id in (select id from deleted);"));
490    }
491
492    #[test]
493    fn output_delete_trigger_cascade_deletes_from_the_child_table_not_itself() {
494        let model = build_model_with_reverse_relation(RelationType::Cascade);
495        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
496
497        let generator = SqlServerTriggerGenerator::new(ctx);
498        generator.output_triggers();
499
500        let output = buffer.contents();
501        assert!(output.contains("delete from app.child where parent_id in (select id from deleted);"));
502    }
503
504    #[test]
505    fn output_delete_trigger_uses_the_relations_own_column_for_a_composite_primary_key() {
506        // The parent has a composite primary key (id, tenant_id); the relation specifically
507        // references `id`. The delete trigger must match on the relation's own referenced
508        // column, not an arbitrarily-picked primary key column.
509        let mut parent = TableBuilder::new(Some("app"), "parent")
510            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
511            .add_column(ColumnBuilder::new(None::<&str>, "tenant_id", ColumnType::Int).required(true).build())
512            .add_key(
513                schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary)
514                    .add_column("id")
515                    .add_column("tenant_id")
516                    .build(),
517            )
518            .build();
519        parent.add_reverse_relation(Relation::new("app.parent", "id", "app.child", "parent_id", RelationType::Cascade, false));
520
521        let child = TableBuilder::new(Some("app"), "child")
522            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
523            .build();
524        let schema = SchemaBuilder::new(Some("app"))
525            .add_table(parent)
526            .add_table(child)
527            .build();
528        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
529        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
530
531        let generator = SqlServerTriggerGenerator::new(ctx);
532        generator.output_triggers();
533
534        let output = buffer.contents();
535        assert!(output.contains("delete from app.child where parent_id in (select id from deleted);"));
536    }
537
538    fn build_model_with_composite_reverse_relation(relation_type: RelationType) -> DatabaseModel {
539        let mut parent = TableBuilder::new(Some("app"), "parent")
540            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
541            .add_column(ColumnBuilder::new(None::<&str>, "tenant_id", ColumnType::Int).required(true).build())
542            .add_key(schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary).add_column("id").build())
543            .build();
544        parent.add_reverse_relation(
545            Relation::new_composite(
546                "app.parent",
547                "app.child",
548                vec![("child_id", "id"), ("child_tenant_id", "tenant_id")],
549                relation_type,
550                false,
551            )
552            .unwrap(),
553        );
554
555        let child = TableBuilder::new(Some("app"), "child")
556            .add_column(ColumnBuilder::new(None::<&str>, "child_id", ColumnType::Int).build())
557            .add_column(ColumnBuilder::new(None::<&str>, "child_tenant_id", ColumnType::Int).build())
558            .add_relation(
559                Relation::new_composite(
560                    "app.parent",
561                    "child",
562                    vec![("child_id", "id"), ("child_tenant_id", "tenant_id")],
563                    relation_type,
564                    false,
565                )
566                .unwrap(),
567            )
568            .build();
569        let schema = SchemaBuilder::new(Some("app"))
570            .add_table(parent)
571            .add_table(child)
572            .build();
573        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
574    }
575
576    #[test]
577    fn output_delete_trigger_enforce_composite_uses_a_join_instead_of_in() {
578        let model = build_model_with_composite_reverse_relation(RelationType::Enforce);
579        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
580
581        let generator = SqlServerTriggerGenerator::new(ctx);
582        generator.output_triggers();
583
584        let output = buffer.contents();
585        assert!(output.contains(
586            "if exists (select 1 from app.child c inner join deleted d on c.child_id = d.id and c.child_tenant_id = d.tenant_id)"
587        ));
588        assert!(output.contains("cannot be deleted. It is being used by a row in the app.child table"));
589    }
590
591    #[test]
592    fn output_delete_trigger_setnull_composite_nulls_all_from_columns() {
593        let model = build_model_with_composite_reverse_relation(RelationType::SetNull);
594        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
595
596        let generator = SqlServerTriggerGenerator::new(ctx);
597        generator.output_triggers();
598
599        let output = buffer.contents();
600        assert!(output.contains(
601            "update app.child set child_id = null, child_tenant_id = null from app.child inner join deleted on app.child.child_id = deleted.id and app.child.child_tenant_id = deleted.tenant_id;"
602        ));
603    }
604
605    #[test]
606    fn output_delete_trigger_cascade_composite_uses_a_join() {
607        let model = build_model_with_composite_reverse_relation(RelationType::Cascade);
608        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
609
610        let generator = SqlServerTriggerGenerator::new(ctx);
611        generator.output_triggers();
612
613        let output = buffer.contents();
614        assert!(output.contains(
615            "delete app.child from app.child inner join deleted on app.child.child_id = deleted.id and app.child.child_tenant_id = deleted.tenant_id;"
616        ));
617    }
618
619    #[test]
620    fn output_update_trigger_composite_checks_all_columns_not_null_then_matches_all_pairs() {
621        let model = build_model_with_composite_reverse_relation(RelationType::Enforce);
622        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::SqlServer, ForeignKeyMode::Triggers);
623
624        let generator = SqlServerTriggerGenerator::new(ctx);
625        generator.output_triggers();
626
627        let output = buffer.contents();
628        assert!(output.contains(
629            "if exists (select 1 from inserted i where i.child_id is not null and i.child_tenant_id is not null and not exists (select 1 from app.parent p where p.id = i.child_id and p.tenant_id = i.child_tenant_id))"
630        ));
631        assert!(output.contains("was not found in the app.parent table"));
632        assert!(output.contains("child_id, child_tenant_id"));
633    }
634}