How To Call A Stored Procedure/Function From A Trigger Function In Postg...

Only 3.9% of viewers are subscribing to my channel 😓.

I request you to please give click on Subscribe button.

It really helps me grow 😢.

Please Like, Comment, and Subscribe to my channel. ❤

Working with triggers is fun and learning. There are endless possibilities that can be achieved through Triggers.

This time, I have explained the usage of Triggers to call other stored procedures or functions in PostgreSQL Trigger Functions.

#trigger #procedure #function #postgresql #database

CREATE TABLE students

(

    roll numeric(10,0),

    name character varying(30),

    course character varying(30)

);

CREATE OR REPLACE PROCEDURE 

trigger_proc(in_value1 IN numeric, in_value2 IN numeric, out_result OUT numeric)

LANGUAGE 'plpgsql'

AS $BODY$

Declare

lv_msg CHARACTER varying(100);

Begin

  out_result := in_value1 + in_value2;

exception

when others then

lv_msg := 'Error : '||sqlerrm;

raise notice '%',lv_msg;

end;

$BODY$;

CREATE OR REPLACE FUNCTION 

trigger_func(in_value1 IN numeric, in_value2 IN numeric) returns numeric

LANGUAGE 'plpgsql'

AS $BODY$

Declare

lv_msg CHARACTER varying(100);

out_result numeric (10);

Begin

  out_result := in_value1 + in_value2;

return out_result;

exception

when others then

lv_msg := 'Error :'||sqlerrm;

raise notice '%',lv_msg;

end;

$BODY$;

CREATE OR REPLACE FUNCTION student_logs_trg_func()

    RETURNS TRIGGER

    LANGUAGE 'plpgsql'

AS $BODY$

declare

lv_out_proc numeric(10);

lv_out_func numeric(10);

begin

call trigger_proc(50, 50, lv_out_proc);

lv_out_func := trigger_func(100, 100);

raise notice 'Proc Output: %',lv_out_proc;

raise notice 'Func Output: %',lv_out_func;

return new;

end;

$BODY$;

CREATE TRIGGER student_trg

    BEFORE INSERT OR DELETE OR UPDATE 

    ON students

    FOR EACH ROW

    EXECUTE FUNCTION student_logs_trg_func();

Insert into students values (1, 'Akram', 'MCA');

How To Call A Stored Procedure/Function From A Trigger Function In PostgreSQL

How To Call A Stored Procedure From A Trigger Function

Trigger Function In PostgreSQL

How To Call Stored Function From Trigger Function

Trigger In PostgreSQL

Call Procedure From Trigger

Call Function From Trigger

Procedure Call From Trigger In PostgreSQL

Function Call From Trigger In PostgreSQL

PostgreSQL Triggers

Triggers

Trigger Example PostgreSQL

How To Triggers PostgreSQL

How To Use/Create Temporary/Temp Tables In PostgreSQL Procedure/Function...

Only 3.9% of viewers are subscribing to my channel 😓.

I request you to please give click on Subscribe button.

It really helps me grow 😢.

Please Like, Comment, and Subscribe to my channel. ❤

-- Global Temporary Tables Usage In PostgreSQL Procedure/Function

-- How To Use Temporary Tables In PostgreSQL Procedure/Function

create or replace procedure temp_tables_proc()

    language plpgsql

    as $$

declare

v_count numeric(10);

begin

create temp table temp_tables(name character varying(100)) on commit preserve rows;

insert into temp_tables values ('Mumbai');

insert into temp_tables values ('Pune');

select count(*) into v_count from temp_tables;

raise notice 'Records : %', v_count;

drop table temp_tables;

exception

when others then

raise notice 'Error : %', substr (sqlerrm, 1, 100);

end;

$$;

call temp_tables_proc();

What Are Global Temporary Tables And Temp Tables In PostgreSQL Database ...

Only 3.9% of viewers are subscribing to my channel 😓.

I request you to please give click on Subscribe button.

It really helps me grow 😢.

Please Like, Comment, and Subscribe to my channel. ❤

-- Global Temporary Tables In PostgreSQL Database

create temporary table temp_location

(

city varchar(50),

street varchar(40)

) on commit delete rows;

select * from temp_location;

create temp table temp_cities

(

name character varying(100)

) on commit delete rows;

select * from temp_cities;

drop table temp_cities;

begin transaction;

insert into temp_cities values ('Mumbai');

insert into temp_cities values ('Pune');

commit;

create temp table temp_cities

(

name character varying(100)

) on commit preserve rows;

begin transaction;

create temp table temp_cities

(

name character varying(100)

) on commit drop;

select * from temp_cities;