Skip navigation links

MySQL Forums :: Triggers :: How to Cancell Delete/Insert/Update with trigger


Advanced Search

How to Cancell Delete/Insert/Update with trigger
Posted by: Widi Wijanarko ()
Date: June 23, 2010 12:49AM

Inside the PostgreSQL, I created a trigger to cancel the command insert / update / delete if certain conditions are not fulfilled,
This example triggernya in PostgreSQL.
like this :

CREATE OR REPLACE FUNCTION Trigger_TblMaster() RETURNS TRIGGER AS $Trigger_TblMaster$
DECLARE
BEGIN
if TG_OP='DELETE' then <<-- delete from tblmster ....
if allowed then
return old;
else
return Null;
end if;
elseif TG_OP='EDIT' then <<-- Update tblmaster set ....
if allowed then
return new;
else
return old;
end if;
ELSE <<-- Insert into tblmaster....
if allowed then
return new;
else
return Null;
end if;
END if;
END;
$Trigger_TblMaster$ LANGUAGE plpgsql;

CREATE TRIGGER Trigger_TblMaster BEFORE INSERT or UPDATE or DELETE ON tblMaster FOR EACH ROW EXECUTE PROCEDURE Trigger_TblMaster();


Now I use MySQL, I want to make the trigger as above.

CREATE TRIGGER BEFORE_INSERT_TBLMASTER BEFORE INSERT ON tblmaster
FOR EACH ROW
BEGIN
if not_Allowed then
....What should I write here if inserted not allowed ???
end if;
end;

CREATE TRIGGER BEFORE_INSERT_TBLMASTER BEFORE UPDATE ON tblmaster
FOR EACH ROW
BEGIN
if not_Allowed then
....What should I write here if updated not allowed ???
end if;
end;

CREATE TRIGGER BEFORE_INSERT_TBLMASTER BEFORE DELETE ON tblmaster
FOR EACH ROW
BEGIN
if not_Allowed then
....What should I write here if deleted not allowed ???
end if;
end;


Will it be three triggers are created to be one like in PostgreSQL?

All the expert .... how do you solve the problem.
Because under certain conditions these two are really I need.

As in MySQL when create table with references nama_table (nama_field) is run if we are using InnoDB, if you use MyISAM can not ... therefore I use t

Options: ReplyQuote


Subject Views Written By Posted
How to Cancell Delete/Insert/Update with trigger 3538 Widi Wijanarko 06/23/2010 12:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.