This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
The following commit(s) were added to refs/heads/main by this push:
new 4490c8c5 Added support for SQLite triggers (#2037)
4490c8c5 is described below
commit 4490c8c55cbe1e6b96a29fd9ba448e06320af50a
Author: Luca Cappelletti <[email protected]>
AuthorDate: Sat Oct 11 13:16:56 2025 +0200
Added support for SQLite triggers (#2037)
Co-authored-by: Ifeanyi Ubah <[email protected]>
---
src/ast/ddl.rs | 56 +++++++--
src/ast/mod.rs | 4 +-
src/dialect/mssql.rs | 6 +-
src/parser/mod.rs | 45 +++++---
tests/sqlparser_mssql.rs | 4 +-
tests/sqlparser_mysql.rs | 4 +-
tests/sqlparser_postgres.rs | 26 ++---
tests/sqlparser_sqlite.rs | 275 ++++++++++++++++++++++++++++++++++++++++++++
8 files changed, 374 insertions(+), 46 deletions(-)
diff --git a/src/ast/ddl.rs b/src/ast/ddl.rs
index aafa8e64..3294a7a8 100644
--- a/src/ast/ddl.rs
+++ b/src/ast/ddl.rs
@@ -2922,6 +2922,26 @@ impl Spanned for RenameTableNameKind {
}
}
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+/// Whether the syntax used for the trigger object (ROW or STATEMENT) is `FOR`
or `FOR EACH`.
+pub enum TriggerObjectKind {
+ /// The `FOR` syntax is used.
+ For(TriggerObject),
+ /// The `FOR EACH` syntax is used.
+ ForEach(TriggerObject),
+}
+
+impl Display for TriggerObjectKind {
+ fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
+ match self {
+ TriggerObjectKind::For(obj) => write!(f, "FOR {obj}"),
+ TriggerObjectKind::ForEach(obj) => write!(f, "FOR EACH {obj}"),
+ }
+ }
+}
+
#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
@@ -2943,6 +2963,23 @@ pub struct CreateTrigger {
///
///
[MsSql](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16#arguments)
pub or_alter: bool,
+ /// True if this is a temporary trigger.
+ ///
+ /// Examples:
+ ///
+ /// ```sql
+ /// CREATE TEMP TRIGGER trigger_name
+ /// ```
+ ///
+ /// or
+ ///
+ /// ```sql
+ /// CREATE TEMPORARY TRIGGER trigger_name;
+ /// CREATE TEMP TRIGGER trigger_name;
+ /// ```
+ ///
+ ///
[SQLite](https://sqlite.org/lang_createtrigger.html#temp_triggers_on_non_temp_tables)
+ pub temporary: bool,
/// The `OR REPLACE` clause is used to re-create the trigger if it already
exists.
///
/// Example:
@@ -2987,6 +3024,8 @@ pub struct CreateTrigger {
/// ```
pub period: TriggerPeriod,
/// Whether the trigger period was specified before the target table name.
+ /// This does not refer to whether the period is BEFORE, AFTER, or INSTEAD
OF,
+ /// but rather the position of the period clause in relation to the table
name.
///
/// ```sql
/// -- period_before_table == true: Postgres, MySQL, and standard SQL
@@ -3006,9 +3045,9 @@ pub struct CreateTrigger {
pub referencing: Vec<TriggerReferencing>,
/// This specifies whether the trigger function should be fired once for
/// every row affected by the trigger event, or just once per SQL
statement.
- pub trigger_object: TriggerObject,
- /// Whether to include the `EACH` term of the `FOR EACH`, as it is
optional syntax.
- pub include_each: bool,
+ /// This is optional in some SQL dialects, such as SQLite, and if not
specified, in
+ /// those cases, the implied default is `FOR EACH ROW`.
+ pub trigger_object: Option<TriggerObjectKind>,
/// Triggering conditions
pub condition: Option<Expr>,
/// Execute logic block
@@ -3025,6 +3064,7 @@ impl Display for CreateTrigger {
fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
let CreateTrigger {
or_alter,
+ temporary,
or_replace,
is_constraint,
name,
@@ -3036,7 +3076,6 @@ impl Display for CreateTrigger {
referencing,
trigger_object,
condition,
- include_each,
exec_body,
statements_as,
statements,
@@ -3044,7 +3083,8 @@ impl Display for CreateTrigger {
} = self;
write!(
f,
- "CREATE {or_alter}{or_replace}{is_constraint}TRIGGER {name} ",
+ "CREATE {temporary}{or_alter}{or_replace}{is_constraint}TRIGGER
{name} ",
+ temporary = if *temporary { "TEMPORARY " } else { "" },
or_alter = if *or_alter { "OR ALTER " } else { "" },
or_replace = if *or_replace { "OR REPLACE " } else { "" },
is_constraint = if *is_constraint { "CONSTRAINT " } else { "" },
@@ -3076,10 +3116,8 @@ impl Display for CreateTrigger {
write!(f, " REFERENCING {}", display_separated(referencing, " "))?;
}
- if *include_each {
- write!(f, " FOR EACH {trigger_object}")?;
- } else if exec_body.is_some() {
- write!(f, " FOR {trigger_object}")?;
+ if let Some(trigger_object) = trigger_object {
+ write!(f, " {trigger_object}")?;
}
if let Some(condition) = condition {
write!(f, " WHEN {condition}")?;
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index 0c83b320..fef8943e 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -70,8 +70,8 @@ pub use self::ddl::{
IdentityParameters, IdentityProperty, IdentityPropertyFormatKind,
IdentityPropertyKind,
IdentityPropertyOrder, IndexColumn, IndexOption, IndexType,
KeyOrIndexDisplay, Msck,
NullsDistinctOption, Owner, Partition, ProcedureParam, ReferentialAction,
RenameTableNameKind,
- ReplicaIdentity, TagsColumnOption, Truncate,
UserDefinedTypeCompositeAttributeDef,
- UserDefinedTypeRepresentation, ViewColumnDef,
+ ReplicaIdentity, TagsColumnOption, TriggerObjectKind, Truncate,
+ UserDefinedTypeCompositeAttributeDef, UserDefinedTypeRepresentation,
ViewColumnDef,
};
pub use self::dml::{Delete, Insert, Update};
pub use self::operator::{BinaryOperator, UnaryOperator};
diff --git a/src/dialect/mssql.rs b/src/dialect/mssql.rs
index 4fcc0e4b..f1d54cd6 100644
--- a/src/dialect/mssql.rs
+++ b/src/dialect/mssql.rs
@@ -18,7 +18,7 @@
use crate::ast::helpers::attached_token::AttachedToken;
use crate::ast::{
BeginEndStatements, ConditionalStatementBlock, ConditionalStatements,
CreateTrigger,
- GranteesType, IfStatement, Statement, TriggerObject,
+ GranteesType, IfStatement, Statement,
};
use crate::dialect::Dialect;
use crate::keywords::{self, Keyword};
@@ -254,6 +254,7 @@ impl MsSqlDialect {
Ok(CreateTrigger {
or_alter,
+ temporary: false,
or_replace: false,
is_constraint: false,
name,
@@ -263,8 +264,7 @@ impl MsSqlDialect {
table_name,
referenced_table_name: None,
referencing: Vec::new(),
- trigger_object: TriggerObject::Statement,
- include_each: false,
+ trigger_object: None,
condition: None,
exec_body: None,
statements_as: true,
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index 2b365e29..70f4d856 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -4753,9 +4753,9 @@ impl<'a> Parser<'a> {
} else if self.parse_keyword(Keyword::DOMAIN) {
self.parse_create_domain()
} else if self.parse_keyword(Keyword::TRIGGER) {
- self.parse_create_trigger(or_alter, or_replace, false)
+ self.parse_create_trigger(temporary, or_alter, or_replace, false)
} else if self.parse_keywords(&[Keyword::CONSTRAINT,
Keyword::TRIGGER]) {
- self.parse_create_trigger(or_alter, or_replace, true)
+ self.parse_create_trigger(temporary, or_alter, or_replace, true)
} else if self.parse_keyword(Keyword::MACRO) {
self.parse_create_macro(or_replace, temporary)
} else if self.parse_keyword(Keyword::SECRET) {
@@ -5551,7 +5551,8 @@ impl<'a> Parser<'a> {
/// DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
/// ```
pub fn parse_drop_trigger(&mut self) -> Result<Statement, ParserError> {
- if !dialect_of!(self is PostgreSqlDialect | GenericDialect |
MySqlDialect | MsSqlDialect) {
+ if !dialect_of!(self is PostgreSqlDialect | SQLiteDialect |
GenericDialect | MySqlDialect | MsSqlDialect)
+ {
self.prev_token();
return self.expected("an object type after DROP",
self.peek_token());
}
@@ -5579,11 +5580,13 @@ impl<'a> Parser<'a> {
pub fn parse_create_trigger(
&mut self,
+ temporary: bool,
or_alter: bool,
or_replace: bool,
is_constraint: bool,
) -> Result<Statement, ParserError> {
- if !dialect_of!(self is PostgreSqlDialect | GenericDialect |
MySqlDialect | MsSqlDialect) {
+ if !dialect_of!(self is PostgreSqlDialect | SQLiteDialect |
GenericDialect | MySqlDialect | MsSqlDialect)
+ {
self.prev_token();
return self.expected("an object type after CREATE",
self.peek_token());
}
@@ -5610,14 +5613,25 @@ impl<'a> Parser<'a> {
}
}
- self.expect_keyword_is(Keyword::FOR)?;
- let include_each = self.parse_keyword(Keyword::EACH);
- let trigger_object =
- match self.expect_one_of_keywords(&[Keyword::ROW,
Keyword::STATEMENT])? {
- Keyword::ROW => TriggerObject::Row,
- Keyword::STATEMENT => TriggerObject::Statement,
- _ => unreachable!(),
- };
+ let trigger_object = if self.parse_keyword(Keyword::FOR) {
+ let include_each = self.parse_keyword(Keyword::EACH);
+ let trigger_object =
+ match self.expect_one_of_keywords(&[Keyword::ROW,
Keyword::STATEMENT])? {
+ Keyword::ROW => TriggerObject::Row,
+ Keyword::STATEMENT => TriggerObject::Statement,
+ _ => unreachable!(),
+ };
+
+ Some(if include_each {
+ TriggerObjectKind::ForEach(trigger_object)
+ } else {
+ TriggerObjectKind::For(trigger_object)
+ })
+ } else {
+ let _ = self.parse_keyword(Keyword::FOR);
+
+ None
+ };
let condition = self
.parse_keyword(Keyword::WHEN)
@@ -5632,8 +5646,9 @@ impl<'a> Parser<'a> {
statements =
Some(self.parse_conditional_statements(&[Keyword::END])?);
}
- Ok(Statement::CreateTrigger(CreateTrigger {
+ Ok(CreateTrigger {
or_alter,
+ temporary,
or_replace,
is_constraint,
name,
@@ -5644,13 +5659,13 @@ impl<'a> Parser<'a> {
referenced_table_name,
referencing,
trigger_object,
- include_each,
condition,
exec_body,
statements_as: false,
statements,
characteristics,
- }))
+ }
+ .into())
}
pub fn parse_trigger_period(&mut self) -> Result<TriggerPeriod,
ParserError> {
diff --git a/tests/sqlparser_mssql.rs b/tests/sqlparser_mssql.rs
index 9dccce49..e11c79f0 100644
--- a/tests/sqlparser_mssql.rs
+++ b/tests/sqlparser_mssql.rs
@@ -2388,6 +2388,7 @@ fn parse_create_trigger() {
create_stmt,
Statement::CreateTrigger(CreateTrigger {
or_alter: true,
+ temporary: false,
or_replace: false,
is_constraint: false,
name: ObjectName::from(vec![Ident::new("reminder1")]),
@@ -2397,8 +2398,7 @@ fn parse_create_trigger() {
table_name: ObjectName::from(vec![Ident::new("Sales"),
Ident::new("Customer")]),
referenced_table_name: None,
referencing: vec![],
- trigger_object: TriggerObject::Statement,
- include_each: false,
+ trigger_object: None,
condition: None,
exec_body: None,
statements_as: true,
diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs
index 9aaa35ba..e0ddecf3 100644
--- a/tests/sqlparser_mysql.rs
+++ b/tests/sqlparser_mysql.rs
@@ -4018,6 +4018,7 @@ fn parse_create_trigger() {
create_stmt,
Statement::CreateTrigger(CreateTrigger {
or_alter: false,
+ temporary: false,
or_replace: false,
is_constraint: false,
name: ObjectName::from(vec![Ident::new("emp_stamp")]),
@@ -4027,8 +4028,7 @@ fn parse_create_trigger() {
table_name: ObjectName::from(vec![Ident::new("emp")]),
referenced_table_name: None,
referencing: vec![],
- trigger_object: TriggerObject::Row,
- include_each: true,
+ trigger_object:
Some(TriggerObjectKind::ForEach(TriggerObject::Row)),
condition: None,
exec_body: Some(TriggerExecBody {
exec_type: TriggerExecBodyType::Function,
diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs
index 3c2a98e1..e18bf662 100644
--- a/tests/sqlparser_postgres.rs
+++ b/tests/sqlparser_postgres.rs
@@ -5636,6 +5636,7 @@ fn parse_create_simple_before_insert_trigger() {
let sql = "CREATE TRIGGER check_insert BEFORE INSERT ON accounts FOR EACH
ROW EXECUTE FUNCTION check_account_insert";
let expected = Statement::CreateTrigger(CreateTrigger {
or_alter: false,
+ temporary: false,
or_replace: false,
is_constraint: false,
name: ObjectName::from(vec![Ident::new("check_insert")]),
@@ -5645,8 +5646,7 @@ fn parse_create_simple_before_insert_trigger() {
table_name: ObjectName::from(vec![Ident::new("accounts")]),
referenced_table_name: None,
referencing: vec![],
- trigger_object: TriggerObject::Row,
- include_each: true,
+ trigger_object: Some(TriggerObjectKind::ForEach(TriggerObject::Row)),
condition: None,
exec_body: Some(TriggerExecBody {
exec_type: TriggerExecBodyType::Function,
@@ -5668,6 +5668,7 @@ fn parse_create_after_update_trigger_with_condition() {
let sql = "CREATE TRIGGER check_update AFTER UPDATE ON accounts FOR EACH
ROW WHEN (NEW.balance > 10000) EXECUTE FUNCTION check_account_update";
let expected = Statement::CreateTrigger(CreateTrigger {
or_alter: false,
+ temporary: false,
or_replace: false,
is_constraint: false,
name: ObjectName::from(vec![Ident::new("check_update")]),
@@ -5677,8 +5678,7 @@ fn parse_create_after_update_trigger_with_condition() {
table_name: ObjectName::from(vec![Ident::new("accounts")]),
referenced_table_name: None,
referencing: vec![],
- trigger_object: TriggerObject::Row,
- include_each: true,
+ trigger_object: Some(TriggerObjectKind::ForEach(TriggerObject::Row)),
condition: Some(Expr::Nested(Box::new(Expr::BinaryOp {
left: Box::new(Expr::CompoundIdentifier(vec![
Ident::new("NEW"),
@@ -5707,6 +5707,7 @@ fn parse_create_instead_of_delete_trigger() {
let sql = "CREATE TRIGGER check_delete INSTEAD OF DELETE ON accounts FOR
EACH ROW EXECUTE FUNCTION check_account_deletes";
let expected = Statement::CreateTrigger(CreateTrigger {
or_alter: false,
+ temporary: false,
or_replace: false,
is_constraint: false,
name: ObjectName::from(vec![Ident::new("check_delete")]),
@@ -5716,8 +5717,7 @@ fn parse_create_instead_of_delete_trigger() {
table_name: ObjectName::from(vec![Ident::new("accounts")]),
referenced_table_name: None,
referencing: vec![],
- trigger_object: TriggerObject::Row,
- include_each: true,
+ trigger_object: Some(TriggerObjectKind::ForEach(TriggerObject::Row)),
condition: None,
exec_body: Some(TriggerExecBody {
exec_type: TriggerExecBodyType::Function,
@@ -5739,6 +5739,7 @@ fn
parse_create_trigger_with_multiple_events_and_deferrable() {
let sql = "CREATE CONSTRAINT TRIGGER check_multiple_events BEFORE INSERT
OR UPDATE OR DELETE ON accounts DEFERRABLE INITIALLY DEFERRED FOR EACH ROW
EXECUTE FUNCTION check_account_changes";
let expected = Statement::CreateTrigger(CreateTrigger {
or_alter: false,
+ temporary: false,
or_replace: false,
is_constraint: true,
name: ObjectName::from(vec![Ident::new("check_multiple_events")]),
@@ -5752,8 +5753,7 @@ fn
parse_create_trigger_with_multiple_events_and_deferrable() {
table_name: ObjectName::from(vec![Ident::new("accounts")]),
referenced_table_name: None,
referencing: vec![],
- trigger_object: TriggerObject::Row,
- include_each: true,
+ trigger_object: Some(TriggerObjectKind::ForEach(TriggerObject::Row)),
condition: None,
exec_body: Some(TriggerExecBody {
exec_type: TriggerExecBodyType::Function,
@@ -5779,6 +5779,7 @@ fn parse_create_trigger_with_referencing() {
let sql = "CREATE TRIGGER check_referencing BEFORE INSERT ON accounts
REFERENCING NEW TABLE AS new_accounts OLD TABLE AS old_accounts FOR EACH ROW
EXECUTE FUNCTION check_account_referencing";
let expected = Statement::CreateTrigger(CreateTrigger {
or_alter: false,
+ temporary: false,
or_replace: false,
is_constraint: false,
name: ObjectName::from(vec![Ident::new("check_referencing")]),
@@ -5799,8 +5800,7 @@ fn parse_create_trigger_with_referencing() {
transition_relation_name:
ObjectName::from(vec![Ident::new("old_accounts")]),
},
],
- trigger_object: TriggerObject::Row,
- include_each: true,
+ trigger_object: Some(TriggerObjectKind::ForEach(TriggerObject::Row)),
condition: None,
exec_body: Some(TriggerExecBody {
exec_type: TriggerExecBodyType::Function,
@@ -5826,7 +5826,7 @@ fn parse_create_trigger_invalid_cases() {
let invalid_cases = vec![
(
"CREATE TRIGGER check_update BEFORE UPDATE ON accounts FUNCTION
check_account_update",
- "Expected: FOR, found: FUNCTION"
+ "Expected: an SQL statement, found: FUNCTION"
),
(
"CREATE TRIGGER check_update TOMORROW UPDATE ON accounts EXECUTE
FUNCTION check_account_update",
@@ -6095,6 +6095,7 @@ fn parse_trigger_related_functions() {
create_trigger,
Statement::CreateTrigger(CreateTrigger {
or_alter: false,
+ temporary: false,
or_replace: false,
is_constraint: false,
name: ObjectName::from(vec![Ident::new("emp_stamp")]),
@@ -6104,8 +6105,7 @@ fn parse_trigger_related_functions() {
table_name: ObjectName::from(vec![Ident::new("emp")]),
referenced_table_name: None,
referencing: vec![],
- trigger_object: TriggerObject::Row,
- include_each: true,
+ trigger_object:
Some(TriggerObjectKind::ForEach(TriggerObject::Row)),
condition: None,
exec_body: Some(TriggerExecBody {
exec_type: TriggerExecBodyType::Function,
diff --git a/tests/sqlparser_sqlite.rs b/tests/sqlparser_sqlite.rs
index 5083ecd0..f0d6d9b7 100644
--- a/tests/sqlparser_sqlite.rs
+++ b/tests/sqlparser_sqlite.rs
@@ -610,6 +610,281 @@ fn test_update_delete_limit() {
}
}
+#[test]
+fn test_create_trigger() {
+ let statement1 = "CREATE TRIGGER trg_inherit_asset_models AFTER INSERT ON
assets FOR EACH ROW BEGIN INSERT INTO users (name) SELECT pam.name FROM users
AS pam; END";
+
+ match sqlite().verified_stmt(statement1) {
+ Statement::CreateTrigger(CreateTrigger {
+ or_alter,
+ temporary,
+ or_replace,
+ is_constraint,
+ name,
+ period,
+ period_before_table,
+ events,
+ table_name,
+ referenced_table_name,
+ referencing,
+ trigger_object,
+ condition,
+ exec_body: _,
+ statements_as,
+ statements: _,
+ characteristics,
+ }) => {
+ assert!(!or_alter);
+ assert!(!temporary);
+ assert!(!or_replace);
+ assert!(!is_constraint);
+ assert_eq!(name.to_string(), "trg_inherit_asset_models");
+ assert_eq!(period, TriggerPeriod::After);
+ assert!(period_before_table);
+ assert_eq!(events, vec![TriggerEvent::Insert]);
+ assert_eq!(table_name.to_string(), "assets");
+ assert!(referenced_table_name.is_none());
+ assert!(referencing.is_empty());
+ assert_eq!(
+ trigger_object,
+ Some(TriggerObjectKind::ForEach(TriggerObject::Row))
+ );
+ assert!(condition.is_none());
+ assert!(!statements_as);
+ assert!(characteristics.is_none());
+ }
+ _ => unreachable!("Expected CREATE TRIGGER statement"),
+ }
+
+ // Here we check that the variant of CREATE TRIGGER that omits the `FOR
EACH ROW` clause,
+ // which in SQLite may be implicitly assumed, is parsed correctly.
+ let statement2 = "CREATE TRIGGER log_new_user AFTER INSERT ON users BEGIN
INSERT INTO user_log (user_id, action, timestamp) VALUES (NEW.id, 'created',
datetime('now')); END";
+
+ match sqlite().verified_stmt(statement2) {
+ Statement::CreateTrigger(CreateTrigger {
+ or_alter,
+ temporary,
+ or_replace,
+ is_constraint,
+ name,
+ period,
+ period_before_table,
+ events,
+ table_name,
+ referenced_table_name,
+ referencing,
+ trigger_object,
+ condition,
+ exec_body: _,
+ statements_as,
+ statements: _,
+ characteristics,
+ }) => {
+ assert!(!or_alter);
+ assert!(!temporary);
+ assert!(!or_replace);
+ assert!(!is_constraint);
+ assert_eq!(name.to_string(), "log_new_user");
+ assert_eq!(period, TriggerPeriod::After);
+ assert!(period_before_table);
+ assert_eq!(events, vec![TriggerEvent::Insert]);
+ assert_eq!(table_name.to_string(), "users");
+ assert!(referenced_table_name.is_none());
+ assert!(referencing.is_empty());
+ assert!(trigger_object.is_none());
+ assert!(condition.is_none());
+ assert!(!statements_as);
+ assert!(characteristics.is_none());
+ }
+ _ => unreachable!("Expected CREATE TRIGGER statement"),
+ }
+
+ let statement3 = "CREATE TRIGGER cleanup_orders AFTER DELETE ON customers
BEGIN DELETE FROM orders WHERE customer_id = OLD.id; DELETE FROM invoices WHERE
customer_id = OLD.id; END";
+ match sqlite().verified_stmt(statement3) {
+ Statement::CreateTrigger(CreateTrigger {
+ or_alter,
+ temporary,
+ or_replace,
+ is_constraint,
+ name,
+ period,
+ period_before_table,
+ events,
+ table_name,
+ referenced_table_name,
+ referencing,
+ trigger_object,
+ condition,
+ exec_body: _,
+ statements_as,
+ statements: _,
+ characteristics,
+ }) => {
+ assert!(!or_alter);
+ assert!(!temporary);
+ assert!(!or_replace);
+ assert!(!is_constraint);
+ assert_eq!(name.to_string(), "cleanup_orders");
+ assert_eq!(period, TriggerPeriod::After);
+ assert!(period_before_table);
+ assert_eq!(events, vec![TriggerEvent::Delete]);
+ assert_eq!(table_name.to_string(), "customers");
+ assert!(referenced_table_name.is_none());
+ assert!(referencing.is_empty());
+ assert!(trigger_object.is_none());
+ assert!(condition.is_none());
+ assert!(!statements_as);
+ assert!(characteristics.is_none());
+ }
+ _ => unreachable!("Expected CREATE TRIGGER statement"),
+ }
+
+ let statement4 = "CREATE TRIGGER trg_before_update BEFORE UPDATE ON
products FOR EACH ROW WHEN NEW.price < 0 BEGIN SELECT RAISE(ABORT, 'Price
cannot be negative'); END";
+ match sqlite().verified_stmt(statement4) {
+ Statement::CreateTrigger(CreateTrigger {
+ or_alter,
+ temporary,
+ or_replace,
+ is_constraint,
+ name,
+ period,
+ period_before_table,
+ events,
+ table_name,
+ referenced_table_name,
+ referencing,
+ trigger_object,
+ condition,
+ exec_body: _,
+ statements_as,
+ statements: _,
+ characteristics,
+ }) => {
+ assert!(!or_alter);
+ assert!(!temporary);
+ assert!(!or_replace);
+ assert!(!is_constraint);
+ assert_eq!(name.to_string(), "trg_before_update");
+ assert_eq!(period, TriggerPeriod::Before);
+ assert!(period_before_table);
+ assert_eq!(events, vec![TriggerEvent::Update(Vec::new())]);
+ assert_eq!(table_name.to_string(), "products");
+ assert!(referenced_table_name.is_none());
+ assert!(referencing.is_empty());
+ assert_eq!(
+ trigger_object,
+ Some(TriggerObjectKind::ForEach(TriggerObject::Row))
+ );
+ assert!(condition.is_some());
+ assert!(!statements_as);
+ assert!(characteristics.is_none());
+ }
+ _ => unreachable!("Expected CREATE TRIGGER statement"),
+ }
+
+ // We test a INSTEAD OF trigger on a view
+ let statement5 = "CREATE TRIGGER trg_instead_of_insert INSTEAD OF INSERT
ON my_view BEGIN INSERT INTO my_table (col1, col2) VALUES (NEW.col1, NEW.col2);
END";
+ match sqlite().verified_stmt(statement5) {
+ Statement::CreateTrigger(CreateTrigger {
+ or_alter,
+ temporary,
+ or_replace,
+ is_constraint,
+ name,
+ period,
+ period_before_table,
+ events,
+ table_name,
+ referenced_table_name,
+ referencing,
+ trigger_object,
+ condition,
+ exec_body: _,
+ statements_as,
+ statements: _,
+ characteristics,
+ }) => {
+ assert!(!or_alter);
+ assert!(!temporary);
+ assert!(!or_replace);
+ assert!(!is_constraint);
+ assert_eq!(name.to_string(), "trg_instead_of_insert");
+ assert_eq!(period, TriggerPeriod::InsteadOf);
+ assert!(period_before_table);
+ assert_eq!(events, vec![TriggerEvent::Insert]);
+ assert_eq!(table_name.to_string(), "my_view");
+ assert!(referenced_table_name.is_none());
+ assert!(referencing.is_empty());
+ assert!(trigger_object.is_none());
+ assert!(condition.is_none());
+ assert!(!statements_as);
+ assert!(characteristics.is_none());
+ }
+ _ => unreachable!("Expected CREATE TRIGGER statement"),
+ }
+
+ // We test a temporary trigger
+ let statement6 = "CREATE TEMPORARY TRIGGER temp_trigger AFTER INSERT ON
temp_table BEGIN UPDATE log_table SET count = count + 1; END";
+ match sqlite().verified_stmt(statement6) {
+ Statement::CreateTrigger(CreateTrigger {
+ or_alter,
+ temporary,
+ or_replace,
+ is_constraint,
+ name,
+ period,
+ period_before_table,
+ events,
+ table_name,
+ referenced_table_name,
+ referencing,
+ trigger_object,
+ condition,
+ exec_body: _,
+ statements_as,
+ statements: _,
+ characteristics,
+ }) => {
+ assert!(!or_alter);
+ assert!(temporary);
+ assert!(!or_replace);
+ assert!(!is_constraint);
+ assert_eq!(name.to_string(), "temp_trigger");
+ assert_eq!(period, TriggerPeriod::After);
+ assert!(period_before_table);
+ assert_eq!(events, vec![TriggerEvent::Insert]);
+ assert_eq!(table_name.to_string(), "temp_table");
+ assert!(referenced_table_name.is_none());
+ assert!(referencing.is_empty());
+ assert!(trigger_object.is_none());
+ assert!(condition.is_none());
+ assert!(!statements_as);
+ assert!(characteristics.is_none());
+ }
+ _ => unreachable!("Expected CREATE TRIGGER statement"),
+ }
+}
+
+#[test]
+fn test_drop_trigger() {
+ let statement = "DROP TRIGGER IF EXISTS trg_inherit_asset_models";
+
+ match sqlite().verified_stmt(statement) {
+ Statement::DropTrigger(DropTrigger {
+ if_exists,
+ trigger_name,
+ table_name,
+ option,
+ }) => {
+ assert!(if_exists);
+ assert_eq!(trigger_name.to_string(), "trg_inherit_asset_models");
+ assert!(table_name.is_none());
+ assert!(option.is_none());
+ }
+ _ => unreachable!("Expected DROP TRIGGER statement"),
+ }
+}
+
fn sqlite() -> TestedDialects {
TestedDialects::new(vec![Box::new(SQLiteDialect {})])
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
