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