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