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 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 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 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 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 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}