Trigger in MySql

Jeyaseelansarc · #1 (**permalink**) 04-18-2008, 11:49 PM

Hi,
I want to implement trigger in mysql for my site. I am very new to this.

can anyone guide me to do this?

Jeyaseelansarc · #2 (**permalink**) 04-25-2008, 05:05 AM

So finally i have found something to do with triggers

Code:

DELIMITER $$

CREATE TRIGGER newsCounter
AFTER INSERT ON News
FOR EACH ROW BEGIN
 INSERT INTO NewsCount (newsItemCount) (SELECT count(*) FROM News);
END;
$$

Jeyaseelansarc · #3 (**permalink**) 04-25-2008, 05:06 AM

Triggers are programmable events that react to queries and reside directly on the database server. Triggers can be executed before or after INSERT, UPDATE or DELETE statements.

Jeyaseelansarc · #4 (**permalink**) 04-25-2008, 05:07 AM

There are several reasons to use triggers. Triggers can automate a lot of stuff that you may have been doing by hand before. The main reason I use triggers is to maintain the integrity of one table that might rely on another table. For example, if TableA references TableB and a value from TableB gets deleted, you could have a trigger setup to handle the data in TableA. Perhaps the data in TableA should be reset to a generic value or maybe the data should just be deleted completely.

Jeyaseelansarc · #5 (**permalink**) 04-25-2008, 05:17 AM

You can only have one trigger per event, per table. In other words, you can't have two triggers that occur AFTER INSERT ON NEWS, but you can have one BEFORE INSERT ON NEWS and another AFTER INSERT ON NEWS.

Jeyaseelansarc · #6 (**permalink**) 04-25-2008, 05:18 AM

You also can't apply a single trigger to multiple events. For example, in Microsoft SQL you can create one trigger that will run after an INSERT or an UPDATE. Unfortunately in MySQL 5.0, you must create an individual trigger for both the INSERT and UPDATE. If you do plan on having the same code run on an INSERT and an UPDATE, it might be better to have your triggers call a stored procedure, so you can keep your code centralized.

Jeyaseelansarc · #7 (**permalink**) 05-12-2008, 02:34 AM

Build Table

You may want to run the following CREATE TABLE statements on a test database, if you plan on following along with the examples. The examples use three tables: NewsCategories, News and NewsCount.

Code:

CREATE TABLE NewsCategories
( catID int not null auto_increment, catName varchar(32), primary key(catID));

CREATE TABLE News
( newsID int not null auto_increment, catID int not null, title
varchar(32) not null, txt blob, primary key(newsID));

CREATE TABLE NewsCount
( newsItemCount int );

Jeyaseelansarc · #8 (**permalink**) 05-12-2008, 02:35 AM

CREATING A TRIGGER

Triggers are relatively easy to create. Let's take a look at the basic syntax of the CREATE TRIGGER statement:

Code:

DELIMITER $$

CREATE TRIGGER newsCounter
AFTER INSERT ON News
FOR EACH ROW BEGIN
 INSERT INTO NewsCount (newsItemCount) (SELECT count(*) FROM News);
END;
$$

Jeyaseelansarc · #9 (**permalink**) 05-12-2008, 02:37 AM

The above trigger will occur after an insert takes place on the 'News' table. When a new item is added to the News table, an INSERT is triggered, adding the count of news items to the NewsCount table.

Jeyaseelansarc · #10 (**permalink**) 05-12-2008, 02:38 AM

There's a couple of basics you need to understand about the syntax. First, the trigger can occur either before or after your query. If you create a trigger that handles INSERTs, you can set it up to run before the database has had the record added, or after. If you want to manipulate data based on a newly inserted AUTO_INCREMENT value, you would have to run it AFTER the INSERT. The NEW keyword acts like a virtual table of the values that are being manipulated on UPDATE and INSERT triggers.
For example, NEW.Title corresponds to the Title field that is replacing (or has replaced) the old value. There is also an OLD keyword that is similar to the NEW keyword, only it uses the old value that had been, or is about to be, updated. The OLD keyword can only be applied to UPDATE and DELETE triggers.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I setup a clock with defined alarm times which trigger another event?	kingmaker	HTML, CSS and Javascript Coding Techniques	1	01-19-2008 03:18 AM
Can we have a commit statement inside a trigger? if no why cant we?	Mramesh	Database Support	6	09-11-2007 02:08 AM
Run an exe from an oracle trigger	H2o	Database Support	5	08-09-2007 10:55 AM
Storing the output from trigger in a text file	sureshbabu	Database Support	3	07-27-2007 05:44 AM
how to create trigger in MYSQL	Jeyaseelansarc	Database Support	5	07-10-2007 10:53 PM