LearningSpot

Overview Triggers are a specialized type of stored procedure that can be written to act on a table action such as an INSERT, UPDATE, or DELETE. If overused, they can potentially lead to performance issues such as blocking and, if they're not written correctly, you could be losing data. Explanation Triggers are commonly used to perform auditing actions, to maintain table integrity in place of native constraints such as foreign keys and check constraints, and to perform other post DML processing. Triggers operate under the scope of a transaction so if a table were updated, for instance, the update would occur and the trigger would fire. While the trigger is at work, the transaction would not be committed until the trigger completed (or rolled back in the case of failure). If a lot of processing is being done in the trigger, locks will be held until the trigger completes. This is an important point to note: Triggers extend the life of a transaction. Also, due to their stealthi...

A SQL trigger is a set of  SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event associated with a table occurs e.g.,  insert, update or delete. A SQL trigger is a special type of  stored procedure . It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly. It is important to understand SQL trigger’s advantages and disadvantages so that you can use it appropriately. In the following sections, we will discuss about the advantages and disadvantages of using SQL trigger CREATE TRIGGER `db1`.trig AFTER INSERT ON `db1`.t1 FOR EACH ROW INSERT INTO `db2`.t1(id,NAME) VALUES(new.id, new.name); See Example below : CREATE DATABASE db1; CREATE DATABASE db2; USE db1; CREATE TABLE t1(id INT(...

A trigger is a rule that you put on a table which basically says, whenever you DELETE, UPDATE or INSERT something in this table, also do something else. For instance, we might want to log a change, but instead of writing two separate queries, one for the change, and one for the log, we can instead write a trigger that says, "Whenever this row is updated, create a new row in a different table to tell me that the update was made". It adds a little overhead to the initial query, but since there are not two packets traveling to your database to do two separate things, there is an overall performance gain CREATE TRIGGER nt AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2(idd, NAMES, ages) VALUES(new.id, new.name, new.age); See Below Example : CREATE DATABASE databasename; USE databasename; CREATE TABLE t1(id INT(10), NAME VARCHAR(30), age INT(10)); INSERT INTO t1 VALUES(001,'raju',20); SELECT * FROM t1; INSERT INTO t1 VALUES(002,'somi',29); CREATE TABLE ...