CODEWITHSUNDEEP
sqltriggers
user3255576
user3255576

Reputation: 11

BEFORE DELETE trigger

How to stop from deleting a row, that has PK in another table (without FK) with a trigger? Is CALL cannot_delete_error would stop from deleting? This is what I've got so far. 

CREATE TRIGGER T1 
BEFORE DELETE ON Clients 
FOR EACH ROW
BEGIN
    SELECT Client, Ref FROM Clients K, Invoice F
    IF F.Client = K.Ref
     CALL cannot_delete_error 
END IF;
END

Upvotes: 1

Views: 481

Answers (2)

peterm
peterm

Reputation: 92785

If you're using MySQL 5.5 or up you can use SIGNAL

DELIMITER //
CREATE TRIGGER tg_fk_check
BEFORE DELETE ON clients
FOR EACH ROW
BEGIN  
  IF EXISTS(SELECT *
              FROM invoices
             WHERE client_id = OLD.client_id) THEN
    SIGNAL sqlstate '45000' 
      SET message_text = 'Cannot delete a parent row: child rows exist';
  END IF;
END//
DELIMITER ;

Here is SQLFiddle demo. Uncomment the last delete and click Build Schema to see it in action.

Upvotes: 0

BlackjacketMack
BlackjacketMack

Reputation: 5692

Use an 'INSTEAD OF DELETE' trigger.

Basically, you can evaluate whether or not you should the delete the item. In the trigger you can ultimately decide to delete the item like:

--test to see if you actually should delete it.


--if you do decide to delete it
DELETE FROM MyTable
WHERE ID IN (SELECT ID FROM deleted)

One side note, remember that the 'deleted' table may be for several rows.

Another side note, try to do this outside of the db if possible! Or with a preceding query. Triggers are downright difficult to maintain. A simple query, or function (e.g. dbo.udf_CanIDeleteThis()') can be much more versatile.

Upvotes: 3

Related Questions