SQL TRIGGER code example

Example 1: CREATE trigger SQL

-- Oracle DB Syntax
CREATE OR REPLACE TRIGGER {trigger_name} 
BEFORE | AFTER 
INSERT | UPDATE | DELETE 
ON {table_name}
(FOR EACH ROW) 
{operation to be executed}

-- for example
CREATE TRIGGER log_updates 
AFTER INSERT OR UPDATE ON Person
FOR EACH ROW 
INSERT INTO LogPerson(ts, IdPerson, surname)
VALUES(CURRENT_TIMESTAMP, NEW.id, NEW.surname);

Example 2: how use trigger in sql

CREATE TRIGGER Product_Details_tr 
BEFORE INSERT ON Product_Details 
FOR EACH ROW 
SET NEW.User_ID = CURRENT_USER();

Example 3: creation d'un trigger sql

CREATE OR REPLACE TRIGGER trigg_example
 BEFORE INSERT OR UPDATE ON table_example
 FOR EACH ROW
 WHEN (new.no_line > 0)
 DECLARE
     evol_exemple number;
 BEGIN
     evol_exemple := :new.exemple  - :old.exemple;
     DBMS_OUTPUT.PUT_LINE('  evolution : ' || evol_exemple);
 END;

Example 4: what is a trigger in sql

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
  
<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name

Example 5: mssql trigger examples

CREATE TRIGGER trigger_name   
ON { Table name or view name }   
[ WITH <Options> ]  
{ FOR | AFTER | INSTEAD OF }   
{ [INSERT], [UPDATE] , [DELETE] }

Example 6: SQL trigger

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
{FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
{sql_statements}