sql trigger example

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