MySQL: DROP TRIGGER Statement

This MySQL tutorial explains how to use the DROP TRIGGER statement to drop a trigger in MySQL with syntax and examples.

Description

Once you have created a trigger in MySQL, you might find that you need to remove it from the database. You can do this with the DROP TRIGGER statement.

Syntax

The syntax to a drop a trigger in MySQL is:

DROP TRIGGER trigger_name;

Parameters or Arguments

trigger_name

The name of the trigger that you wish to drop.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE UPDATE triggers.

Example

Let's look at an example of how to drop a trigger in MySQL.

For example:

DROP TRIGGER orders_before_insert;

This example uses the ALTER TRIGGER statement to drop the trigger called orders_before_insert.

 

MySQL: AFTER DELETE Trigger

This MySQL tutorial explains how to create an AFTER DELETE Trigger in MySQL with syntax and examples.

Description

An AFTER DELETE Trigger means that MySQL will fire this trigger after the DELETE operation is executed.

Syntax

The syntax to create an AFTER DELETE Trigger in MySQL is:

CREATE TRIGGER trigger_name
AFTER DELETE
   ON table_name FOR EACH ROW

BEGIN

   -- variable declarations

   -- trigger code

END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

AFTER DELETE

It indicates that the trigger will fire after the DELETE operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create an AFTER trigger on a view.
• You can not update the NEW values.
• You can not update the OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an AFTER DELETE trigger using the CREATE TRIGGER statement in MySQL.

If you had a table created as follows:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  created_date DATE,
  created_by VARCHAR(30),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

We could then use the CREATE TRIGGER statement to create an AFTER DELETE trigger as follows:

DELIMITER //

CREATE TRIGGER contacts_after_delete
AFTER DELETE
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing the DELETE into table
   SELECT USER() INTO vUser;

   -- Insert record into audit table
   INSERT INTO contacts_audit
   ( contact_id,
     deleted_date,
     deleted_by)
   VALUES
   ( OLD.contact_id,
     SYSDATE(),
     vUser );

END; //

DELIMITER ;

 

MySQL: BEFORE DELETE Trigger

This MySQL tutorial explains how to create a BEFORE DELETE Trigger in MySQL with syntax and examples.

Description

A BEFORE DELETE Trigger means that MySQL will fire this trigger before the DELETE operation is executed.

Syntax

The syntax to create a BEFORE DELETE Trigger in MySQL is:

CREATE TRIGGER trigger_name
BEFORE DELETE
   ON table_name FOR EACH ROW

BEGIN

   -- variable declarations

   -- trigger code

END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

BEFORE DELETE

It ndicates that the trigger will fire before the DELETE operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create a BEFORE trigger on a view.
• You can update the NEW values.
• You can not update the OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an BEFORE DELETE trigger using the CREATE TRIGGER statement in MySQL.

If you had a table created as follows:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  created_date DATE,
  created_by VARCHAR(30),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

We could then use the CREATE TRIGGER statement to create an BEFORE DELETE trigger as follows:

DELIMITER //

CREATE TRIGGER contacts_before_delete
BEFORE DELETE
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing the DELETE into table
   SELECT USER() INTO vUser;

   -- Insert record into audit table
   INSERT INTO contacts_audit
   ( contact_id,
     deleted_date,
     deleted_by)
   VALUES
   ( OLD.contact_id,
     SYSDATE(),
     vUser );

END; //

DELIMITER ;

 

MySQL: AFTER UPDATE Trigger

This MySQL tutorial explains how to create an AFTER UPDATE Trigger in MySQL with syntax and examples.

Description

An AFTER UPDATE Trigger means that MySQL will fire this trigger after the UPDATE operation is executed.

Syntax

The syntax to create an AFTER UPDATE Trigger in MySQL is:

CREATE TRIGGER trigger_name
AFTER UPDATE
   ON table_name FOR EACH ROW

BEGIN

   -- variable declarations

   -- trigger code

END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

AFTER UPDATE

It indicates that the trigger will fire after the UPDATE operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create an AFTER trigger on a view.
• You can not update the NEW values.
• You can not update the OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an AFTER UPDATE trigger using the CREATE TRIGGER statement in MySQL.

If you had a table created as follows:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  created_date DATE,
  created_by VARCHAR(30),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

We could then use the CREATE TRIGGER statement to create an AFTER UPDATE trigger as follows:

DELIMITER //

CREATE TRIGGER contacts_after_update
AFTER UPDATE
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing the INSERT into table
   SELECT USER() INTO vUser;

   -- Insert record into audit table
   INSERT INTO contacts_audit
   ( contact_id,
     updated_date,
     updated_by)
   VALUES
   ( NEW.contact_id,
     SYSDATE(),
     vUser );

END; //

DELIMITER ;

 

MySQL: BEFORE UPDATE Trigger

This MySQL tutorial explains how to create a BEFORE UPDATE Trigger in MySQL with syntax and examples.

Description

A BEFORE UPDATE Trigger means that MySQL will fire this trigger before the UPDATE operation is executed.

Syntax

The syntax to create a BEFORE UPDATE Trigger in MySQL is:

CREATE TRIGGER trigger_name
BEFORE UPDATE
   ON table_name FOR EACH ROW

BEGIN

   -- variable declarations

   -- trigger code

END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

BEFORE UPDATE

It indicates that the trigger will fire before the UPDATE operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create a BEFORE trigger on a view.
• You can update the NEW values.
• You can not update the OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an BEFORE UPDATE trigger using the CREATE TRIGGER statement in MySQL.

If you had a table created as follows:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  created_date DATE,
  created_by VARCHAR(30),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

We could then use the CREATE TRIGGER statement to create an BEFORE UPDATE trigger as follows:

DELIMITER //

CREATE TRIGGER contacts_before_update
BEFORE UPDATE
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing the INSERT into table
   SELECT USER() INTO vUser;

   -- Insert record into audit table
   INSERT INTO contacts_audit
   ( contact_id,
     updated_date,
     updated_by)
   VALUES
   ( NEW.contact_id,
     SYSDATE(),
     vUser );

END; //

DELIMITER ;

 

MySQL: AFTER INSERT Trigger

This MySQL tutorial explains how to create an AFTER INSERT Trigger in MySQL with syntax and examples.

Description

An AFTER INSERT Trigger means that MySQL will fire this trigger after the INSERT operation is executed.

Syntax

The syntax to create an AFTER INSERT Trigger in MySQL is:

CREATE TRIGGER trigger_name
AFTER INSERT
   ON table_name FOR EACH ROW

BEGIN

   -- variable declarations

   -- trigger code

END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

AFTER INSERT

It indicates that the trigger will fire after the INSERT operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create an AFTER trigger on a view.
• You can not update the NEW values.
• You can not update the OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an AFTER INSERT trigger using the CREATE TRIGGER statement in MySQL.

If you had a table created as follows:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

We could then use the CREATE TRIGGER statement to create an AFTER INSERT trigger as follows:

DELIMITER //

CREATE TRIGGER contacts_after_insert
AFTER INSERT
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing the INSERT into table
   SELECT USER() INTO vUser;

   -- Insert record into audit table
   INSERT INTO contacts_audit
   ( contact_id,
     created_date,
     created_by)
   VALUES
   ( NEW.contact_id,
     SYSDATE(),
     vUser );

END; //

DELIMITER ;

 

MySQL: BEFORE INSERT Trigger

This MySQL tutorial explains how to create a BEFORE INSERT Trigger in MySQL with syntax and examples.

Description

A BEFORE INSERT Trigger means that MySQL will fire this trigger before the INSERT operation is executed.

Syntax

The syntax to create a BEFORE INSERT Trigger in MySQL is:

CREATE TRIGGER trigger_name
BEFORE INSERT
   ON table_name FOR EACH ROW

BEGIN

   -- variable declarations

   -- trigger code

END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

BEFORE INSERT

It indicates that the trigger will fire before the INSERT operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create a BEFORE trigger on a view.
• You can update the NEW values.
• You can not update the OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an BEFORE INSERT trigger using the CREATE TRIGGER statement in MySQL.

If you had a table created as follows:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  created_date DATE,
  created_by VARCHAR(30),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

We could then use the CREATE TRIGGER statement to create an BEFORE INSERT trigger as follows:

DELIMITER //

CREATE TRIGGER contacts_before_insert
BEFORE INSERT
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing INSERT into table
   SELECT USER() INTO vUser;

   -- Update create_date field to current system date
   SET NEW.created_date = SYSDATE();

   -- Update created_by field to the username of the person performing the INSERT
   SET NEW.created_by = vUser;

END; //

DELIMITER ;

 

MySQL: Triggers

The following is a list of topics that explain how to use Triggers in MySQL:

Create Trigger

• BEFORE INSERT Trigger
• AFTER INSERT Trigger
• BEFORE UPDATE Trigger
• AFTER UPDATE Trigger
• BEFORE DELETE Trigger
• AFTER DELETE Trigger

Drop Trigger

• Drop a Trigger

 

MySQL: Procedures

This MySQL tutorial explains how to create and drop procedures in MySQL with syntax and examples.

What is a procedure in MySQL?

In MySQL, a procedure is a stored program that you can pass parameters into. It does not return a value like a function does.

Create Procedure

Just as you can create procedures in other languages, you can create your own procedures in MySQL. Let's take a closer look.

Syntax

The syntax to create a procedure in MySQL is:

CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]

BEGIN

   declaration_section

   executable_section

END;

procedure_name

The name to assign to this procedure in MySQL.

parameter

Optional. One or more parameters passed into the procedure. When creating a procedure, there are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure. The value of the parameter can not be overwritten by the procedure.
2. OUT - The parameter can not be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.
3. IN OUT - The parameter can be referenced by the procedure and the value of the parameter can be overwritten by the procedure.

declaration_section

The place in the procedure where you declare local variables.

executable_section

The place in the procedure where you enter the code for the procedure.

Example

Let's look at an example that shows how to create a procedure in MySQL:

DELIMITER //

CREATE procedure CalcIncome ( OUT ending_value INT )

BEGIN

   DECLARE income INT;

   SET income = 50;

   label1: WHILE income <= 3000 DO
     SET income = income * 2;
   END WHILE label1;

   SET ending_value = income;

END; //

DELIMITER ;

You could then reference your new procedure as follows:

CALL CalcIncome (@variable_name);

SELECT @variable_name;

Drop procedure

Once you have created your procedure in MySQL, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a procedure in MySQL is:

DROP procedure [ IF EXISTS ] procedure_name;

procedure_name

The name of the procedure that you wish to drop.

Example

Let's look at an example of how to drop a procedure in MySQL.

For example:

DROP procedure CalcIncome;

This example would drop the procedure called CalcIncome.

MySQL: Functions

This MySQL tutorial explains how to create and drop functions in MySQL with syntax and examples.

What is a function in MySQL?

In MySQL, a function is a stored program that you can pass parameters into and then return a value.

Create Function

Just as you can create functions in other languages, you can create your own functions in MySQL. Let's take a closer look.

Syntax

The syntax to create a function in MySQL is:

CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype

BEGIN

   declaration_section

   executable_section

END;

function_name

The name to assign to this function in MySQL.

parameter

One or more parameters passed into the function. When creating a function, all parameters are considered to be IN parameters (not OUT or INOUT parameters) where the parameters can be referenced by the function but can not be overwritten by the function.

return_datatype

The data type of the function's return value.

declaration_section

The place in the function where you declare local variables.

executable_section

The place in the function where you enter the code for the function.

Example

Let's look at an example that shows how to create a function in MySQL:

DELIMITER //

CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT

BEGIN

   DECLARE income INT;

   SET income = 0;

   label1: WHILE income <= 3000 DO
     SET income = income + starting_value;
   END WHILE label1;

   RETURN income;

END; //

DELIMITER ;

You could then reference your new function as follows:

SELECT CalcIncome (1000);

Drop Function

Once you have created your function in MySQL, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a function in MySQL is:

DROP FUNCTION [ IF EXISTS ] function_name;

function_name

The name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in MySQL.

For example:

DROP FUNCTION CalcIncome;

This example would drop the function called CalcIncome.