تحقیقات

آموزش trigger در mysql

• توسط : علی حسین شهابی
• 0 نظر

با سلام

یکی از بحث های جالب در مای اس کیوال (آموزش mysql) تریگرها (trigger ) هستند.

تریگرها امکان جدیدیست که از نسخه 5.0 به MySQL اضافه شده ولی در این نسخه محدودیتهایی وجود داشت که در نسخه 5.1 برطرف شده است بهتر است از نسخه 5.1 استفاده کنید , امکاناتی که تریگرها در اختیار ما قرار می دهند رو میشه به 3 قسمت تقسیم بندی کرد :

• شما از اینها می توانید برای چک کردن و یا لغو کردن ورود اطلاعات غیر مفید به بانک اطلاعاتی خود استفاده کنید .
• می توانید از این امکان برای تغییر دادن و یا بی اثر کردن جملات INSERT , UPDATE و یا DELETE استفاده کنید .
• امکان مفیدی است برای مانیتور کردن تغییرات اعمال شده روی بانک اطلاعاتی .

برای بهتر کارکردن با این امکان شما باید ابتدا اطلاعاتی در مورد روالهای ذخیره شده ( Stored Procedures ) داشته باشید زیرا نحوه نوشتن تریگرها تا حدود زیادی مشابه نوشتن رواللهای ذخیره شده است به طور مثال :

• استفاده از جملات ترکیبی (BEGIN / END) ( که برای مشخص کردن شروع و پایان دستورات می باشد) مجاز است .
• استفاده از عبارات کنترلی همانند IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE مجاز می باشد.
• تعریف متغیر (DECLARE) و مقدار دهی آن ( امکان پذیر است .

 

CREATE TRIGGER نام تریگر
	{ BEFORE | AFTER }
	{ INSERT | UPDATE | DELETE }
	ON نام جدول
	FOR EACH ROW
       //code

 

• در قسمت “نام” باید نامtrigger رو قرار دهید .
• در قسمت  ” نام جدول” باید نام جدولی که می خواهید trigger روی آن حساس باشد را قرار دهید .
• در قسمت code هم باید بدنه ی trigger را بنویسید .

 

مشخصات تریگرها :

نام : تریگرها می بایست حتما دارای نام باشند و البته کمتر از 64 حرف و می توانید از جدا کننده هایی استفاده کنید همانند نامهای دیگر در MySQL.
می توانید از ساختار مشابه زیر استفاده کنید :
نام جدول + ‘_’ + نوع تریگر . به طور مثال اگر اسم جدول t29 بوده و می خواهید این تریگر در زمان قبل(BEFORE) از UPDATE اجرا شود از نام t29_bu برای تریگر خود استفاده کنید که البته ابن اختیاریست و می توانید از هر نام دیگری نیز استفاده کنید.

زمان : تریگرها دارای زمان فعالیت می باشند که می توانند قبل و یا بعد از یک عملی اجرا شوند که توسط قسمت { BEFORE | AFTER } INSERT | UPDATE | DELETE } زمان آن مشخص می شود .
به طور مثال اگر بخواهید آن را قبل از عمل حذف اجرا کنید از ” BEFORE DELETE ” استفاده می کنید.

رویداد : که یکی از اعمال INSERT , UPDATE و یا DELETE است که در قسمت قبل توضیح داده شد.

جدول : تریگرها می باست مربوط به جول باشند که در هر INSERT , UPDATE و یا DELETE اجرا شده روی آن جدولtrigger مربوطه آن اجرا شود . البته به خاطر داشته باشید که نمی توانید دو تریگر برای یک جدول و یک زمان مشابه بسازید.

سطرهای جدول : عبارت FOR EACH ROW حاکی از این است که تریگرها روی سطرهای جدول اجرا می شوند نه روی تمام جدول.

عبارات : تریگرها می توانند شامل هیچ عبارتی نباشند و هم می توانند شامل عبارت باشند و شامل همه آن محدودیتای توابع که قبلا گفته شد.

ستونهای NEW و OLD : درtrigger میتوانید به هر سطر جدول ارجاع داشته باشید ولی نمی توانید فقط نام ستون را مشخص کنید چون باعث ابهام خواهد شد . به همین خاطر از NEW و OLD استفاده می کنیم . NEW مقدار جدید و OLD نمایانگر مقدار قدیمی است. و به صورت زیر از آنها استفاده می کنیم:
NEW | OLD “.” نام ستون

در موقع INSERT فقط NEW و در موقع DELETE فقط OLD مجاز است ولی در موقع UPDATE استفاده از هر دو اینها مجاز است . در زیر یک مثال از نحوه استفاده آن آمده :

CREATE TRIGGER t21_au 
	BEFORE UPDATE 
	ON t22 
	FOR EACH ROW 
	BEGIN 
	   SET @old = OLD. s1;
	   SET @new = NEW.s1;
	END;//

حال اگر جدول t22 شامل یک سطر با مقدار 55 در ستون s1 خود باشد بعد از اجرای دستور ” UPDATE t21 SET s1 = s1 + 1 ” و وقتی بروزرسانی به پایان برسد متغیر @old مقدار 55 و متغیر @new مقدار 56 را دارا خواهد بود.

DELIMITER // 
	CREATE TABLE t22 (s1 INTEGER)// 
	CREATE TRIGGER t22_bi 
	BEFORE INSERT 
	ON t22 
	FOR EACH ROW 
	BEGIN 
		SET @x = 'Trigger was activated!';
		SET NEW.s1 = 55;
	END;//

 

ابتدا جدولی به نام t22 ایجاد کرده که شدارای یک ستون از نوع عددی بوده به نام s1 و trigger ای که قبل از انجام هر INSERT اجرا شده و مقدار ستون s1 را 55 قرار می دهد .

حال یک sql را اجرا کرده تا نحوه کار اینtrigger را بهتر متوجه شوید:
INSERT INTO t22 VALUES (1)//

حال اگر با دستور SELECT اطلاعات این جدول را خراج کنید می بینید که مقدار ستون s1 همیشه 55 خواهد بود.

SELECT @x, t22.* FROM t22//
	+------------------------+------+ 
	| @x 					 | s1   |
	+------------------------+------+ 
	| Trigger was activated! | 55   | 
	+------------------------+------+ 
	1 row in set (0.00 sec)

برای مشاهده ی تمام trigger ها از دستور زیر استفاده می نماییم :

SHOW TRIGGERS ;

این تنها آشنایی مختصری باtrigger ها بوده و برای اطلاعات بیشتر می توانید از منوالهای MySQL که در سایت آن http://www.mysql.com موجود است استفاده کنید.

در پایان مثالی که در منوال MySQL آمده است را قرار داده تا با یک نمونه عملی آشنا شوید.

CREATE TABLE test1(a1 INT);
	CREATE TABLE test2(a2 INT);
	CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
	CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,b4 INT DEFAULT 0 ); 
	
	DELIMITER |


	CREATE TRIGGER testref 
	BEFORE INSERT ON test1  
	FOR EACH ROW 
	BEGIN 
	 INSERT INTO test2 SET a2 = NEW.a1; 
	 DELETE FROM test3 WHERE a3 = NEW.a1; 
	 UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
	END;|


 	DELIMITER ;
	
	INSERT INTO test3 (a3) VALUES 
		(NULL), (NULL), (NULL), (NULL), 
		(NULL), (NULL), (NULL), (NULL),
		(NULL), (NULL); 


   	INSERT INTO test4 (a4) VALUES  
		(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);  


	
	mysql> INSERT INTO test1 VALUES
       -> (1), (3), (1), (7), (1), (8), (4), (4);
   	Query OK, 8 rows affected (0.01 sec)  
	Records: 8  Duplicates: 0  Warnings: 0 

	mysql> SELECT * FROM test1;
   +------+
   | a1   |
   +------+
   |    1 |
   |    3 |
   |    1 |
   |    7 |
   |    1 |
   |    8 |
   |    4 |
   |    4 |
   +------+
  8 rows in set (0.00 sec)

  mysql> SELECT * FROM test2;
  +------+
  | a2   |
  +------+
  |    1 |
  |    3 |
  |    1 |
  |    7 |
  |    1 |
  |    8 |
  |    4 |
  |    4 |
  +------+
  8 rows in set (0.00 sec)

  mysql> SELECT * FROM test3;
  +----+
  | a3 |
  +----+
  |  2 |
  |  5 |
  |  6 |
  |  9 |
  | 10 |
  +----+
  5 rows in set (0.00 sec)

  mysql> SELECT * FROM test4;
  +----+------+
  | a4 | b4   |
  +----+------+
  |  1 |    3 |
  |  2 |    0 |
  |  3 |    1 |
  |  4 |    2 |
  |  5 |    0 |
  |  6 |    0 |
  |  7 |    1 |
  |  8 |    1 |
  |  9 |    0 |
  | 10 |    0 |
  +----+------+
  10 rows in set (0.00 sec)

امیدوارم مفید بوده باشه..

موفق باشید .