TRIGGER

MySQL

Example

CREATE TRIGGER employees_salary_update
  BEFORE UPDATE ON employees
  FOR EACH ROW
  BEGIN
     SET NEW.salary = NEW.salary + 1000;
  END;

Output

Query OK, 0 rows affected (0.00 sec)

Explanation

In the provided example, a trigger named employees_salary_update is created. This trigger operates before an update event on the employees table. For each row being updated, the trigger will add 1000 to the new value of the salary column. The output simply confirms that the trigger was created successfully, with no rows affected since the trigger took no immediate action.

PostgreSQL

Example

CREATE TRIGGER employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE PROCEDURE log_employee_update();

Output

Query returned successfully: one row affected, 130 msec execution time.

Explanation

This is a simple PostgreSQL trigger definition. It creates a trigger named employee_update that runs before each UPDATE operation on the employees table. This trigger calls the plpgsql function log_employee_update(). This function has to be defined separately and is used to perform specific actions each time the trigger is activated. The output shows that the trigger creation was successful.

SQL Server

Example

CREATE TRIGGER Purchases
ON Orders
AFTER INSERT
AS
PRINT 'A new order has been placed.';
GO
INSERT INTO Orders (OrderId, ProductId, Quantity)
VALUES (1, 101, 3);

Output

A new order has been placed.

Explanation

The TRIGGER Purchases is applied to the Orders table and fires after an INSERT operation. Here, when a new row is inserted into the Orders table, SQL Server triggers and executes the PRINT statement displaying ‘A new order has been placed.’.

Oracle

Example

CREATE OR REPLACE TRIGGER sales_alert
BEFORE INSERT ON sales
FOR EACH ROW
BEGIN
   IF :new.total > 10000 THEN
      INSERT INTO sales_alerts(order_id, alert_date)
      VALUES(:new.order_id, SYSDATE);
   END IF;
END;
/

Output

No direct output is given because triggers in Oracle run behind the scenes.

Explanation

The TRIGGER sales_alert is created for the table sales. This trigger checks each new row inserted into the sales table. If the total field of this new entry is greater than 10000, it inserts a record in the sales_alerts table with the order_id of the new sale and the current date (SYSDATE).

SQLite

Example

CREATE TRIGGER product_before_insert
BEFORE INSERT ON Products
BEGIN
   SELECT RAISE(ROLLBACK, 'Cannot insert NULL into Products')
   WHERE NEW.ProductName IS NULL;
END;

Output

There is no output for the trigger creation. But once this trigger is set, trying to insert null into ProductName column of Products table will raise an error.

Explanation

In the provided example, a trigger product_before_insert is defined. It aims to protect the Products table from having a NULL value inserted into the ProductName column. The trigger is set to activate BEFORE INSERT ON Products which means it will execute before any insertion occurs on the Products table. If an attempt is made to insert a NULL value into the ProductName column, the RAISE function will cause the transaction to rollback and return an error message: ‘Cannot insert NULL into Products’. Thus, this trigger helps maintain data integrity in the Products table.