postgres trigger code example

Example 1: example of trigger in postgresql

-- Trigger to update donatiom count in donor table whenever
-- A new donation is made by that person

CREATE or REPLACE FUNCTION increase_count()
RETURNS TRIGGER
AS
$$
BEGIN
	UPDATE donor SET dcount = dcount + 1 WHERE did = NEW.did;
END
$$
LANGUAGE plpgsql;



CREATE TRIGGER update_donation_count AFTER INSERT ON donation
FOR EACH ROW
EXECUTE PROCEDURE increase_count();

Example 2: how to drop a trigger in postgresql

DROP TRIGGER [IF EXISTS] trigger-name 
ON table-name [ CASCADE | RESTRICT ];

Example 3: mysql create trigger

CREATE
    TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `database`.`table`
    FOR EACH ROW BEGIN
		-- trigger body
		-- this code is applied to every 
		-- inserted/updated/deleted row
    END;
    
CREATE
	TRIGGER `blog_after_insert` AFTER INSERT 
	ON `blog` 
	FOR EACH ROW BEGIN
	
		IF NEW.deleted THEN
			SET @changetype = 'DELETE';
		ELSE
			SET @changetype = 'NEW';
		END IF;
    
		INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
		
    END$$

Example 4: mssql trigger

-- 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