1-- SQL Server (update my_table after update on my_table)
2CREATE TRIGGER trigger_name ON my_table AFTER UPDATE AS
3IF UPDATE (col_name) -- Optional, for particular column
4BEGIN
5 UPDATE my_table SET my_col_date = getdate() FROM my_table
6END;
7-- Oracle (insert into log table after update on my_table)
8CREATE OR REPLACE TRIGGER trigger_name AFTER UPDATE ON my_table
9FOR EACH ROW
10BEGIN
11 INSERT INTO my_log_table (LOG_DATE, ACTION) VALUES (SYSDATE, 'Changed');
12END;
1CREATE TRIGGER Product_Details_tr
2BEFORE INSERT ON Product_Details
3FOR EACH ROW
4SET NEW.User_ID = CURRENT_USER();
1-- Oracle DB Syntax
2CREATE OR REPLACE TRIGGER {trigger_name}
3BEFORE | AFTER
4INSERT | UPDATE | DELETE
5ON {table_name}
6(FOR EACH ROW)
7{operation to be executed}
8
9-- for example
10CREATE TRIGGER log_updates
11AFTER INSERT OR UPDATE ON Person
12FOR EACH ROW
13INSERT INTO LogPerson(ts, IdPerson, surname)
14VALUES(CURRENT_TIMESTAMP, NEW.id, NEW.surname);
1CREATE TRIGGER trigger_name
2ON { Table name or view name }
3[ WITH <Options> ]
4{ FOR | AFTER | INSTEAD OF }
5{ [INSERT], [UPDATE] , [DELETE] }
1CREATE TRIGGER production.trg_product_audit
2ON production.products
3AFTER INSERT, DELETE
4AS
5BEGIN
6 SET NOCOUNT ON;
7 INSERT INTO production.product_audits(
8 product_id,
9 product_name,
10 brand_id,
11 category_id,
12 model_year,
13 list_price,
14 updated_at,
15 operation
16 )
17 SELECT
18 i.product_id,
19 product_name,
20 brand_id,
21 category_id,
22 model_year,
23 i.list_price,
24 GETDATE(),
25 'INS'
26 FROM
27 inserted i
28 UNION ALL
29 SELECT
30 d.product_id,
31 product_name,
32 brand_id,
33 category_id,
34 model_year,
35 d.list_price,
36 GETDATE(),
37 'DEL'
38 FROM
39 deleted d;
40END
41Code language: SQL (Structured Query Language) (sql)
1-- SQL Server Syntax
2-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
3
4CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
5ON { table | view }
6[ WITH <dml_trigger_option> [ ,...n ] ]
7{ FOR | AFTER | INSTEAD OF }
8{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
9[ WITH APPEND ]
10[ NOT FOR REPLICATION ]
11AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
12
13<dml_trigger_option> ::=
14 [ ENCRYPTION ]
15 [ EXECUTE AS Clause ]
16
17<method_specifier> ::=
18 assembly_name.class_name.method_name
19
20