CODEWITHSUNDEEP
postgresqltriggers
Ice
Ice

Reputation: 1971

Inserting new row in another table with trigger

I have problem with trigger in postgresql. I have two tables.

    CREATE TABLE Worker (
  idWorker SERIAL  NOT NULL ,
  Name VARCHAR    ,
  LastName VARCHAR    ,
  Spec VARCHAR      ,
PRIMARY KEY(idWorker));

CREATE TABLE Boss (
  idBoss SERIAL  NOT NULL ,
  Name VARCHAR    ,
  LastName VARCHAR      ,
  Password VARCHAR,
PRIMARY KEY(idBoss));

I wrote function

create function addBoss() returns trigger LANGUAGE 'plpgsql' as'
DECLARE
password varchar;
BEGIN
password = tg_argv [0];
insert into Boss(name,LastName,password) values(new.imie,new.lastname,password);
RETURN NULL;
END;
';

And now, I should create the trigger, but I don't know how. The trigger should create new row in Boss table with the same name and lastname like in Worker table but password should be random after insert new data to Worker table.

Upvotes: 0

Views: 2285

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Create trigger

Manual is very helpful in this case and does contain examples to which you may refer.

Correct syntax (as of 9.5) is :

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

I don't really see a use of TG_ARGV[] special variable. You could generate your random password inside the procedure called for trigger, or use an external one and call it within.

For your particular case that statement could look like:

CREATE TRIGGER after_insert_worker 
  AFTER INSERT ON worker
  FOR EACH ROW
  EXECUTE PROCEDURE addboss();

I assumed an AFTER TRIGGER because you're using RETURN NULL which is ignored in an after trigger, and I see that you want to insert a row into your worker table.

For the sake of completness of this answer, correct way to pass arguments to a function executed by trigger would be to pass them just like normal input values to a regular function, which would be:

EXECUTE PROCEDURE proc_name('trigger_arg1', ...)

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

Bold emphasis mine.

Random strings

This topic has been broached many times here. There are many ways to achieve this goal, none of which is perfect, but I doubt there is any perfect solution. As long as it gets the job done for you, consider it sufficient.

Upvotes: 0

Related Questions