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