Bug #8751 Triggers: Allowed to create triggers on a subject table in a different DB.
Submitted: 23 Feb 2005 22:28 Modified: 20 Jul 2005 15:45
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.2 OS:Windows (Windows 2003)
Assigned to: Antony Curtis CPU Architecture:Any

[23 Feb 2005 22:28] Disha 
Description:
Currently creation of a trigger referencing a subject table residing in another database is successful.

How to repeat:
Setup:
1. Create two databases say 'd1' and 'd2'
2. Create table 't1' in database 'd1' with fields say 'name' and 'ph_no'
3. Create table 't2' in database 'd2' with fields say 'lastname' and 'address'

Steps:

1. Use the database 'd2' i.e. execute the following SQL statement:	"use d2;"
2. Create a trigger say 'trigger_1' on table 't1' from database 'd1' by executing the following SQL statememts:
"CREATE TRIGGER trigger_1 BEFORE INSERT ON d1.t1 FOR EACH ROW SET New.ph_no=200000;"

    
Expected Result
The creation of a trigger having subject table in different database should fail with an appropriate error

Actual Result
The trigger creation completes successfully without any errors

Additional Information
Although the Trigger is created when database 'd2' is in use, the trigger gets created in database 'd1' as it has its subject table in 'd1'
[23 Feb 2005 23:01] Disha 
Changing the severity
[24 Feb 2005 9:29] Disha 
Updated the Synopsis.
[24 Feb 2005 12:45] Alexander Keremidarski 
>Expected Result
>The creation of a trigger having subject table in different database should fail
>with an appropriate error

>Actual Result
>The trigger creation completes successfully without any errors

Why so?

Note that current TRIGGER implementation in 5.0 is rudimentary as MySQL Manual explains. Their behaviour may change in the future.
[24 Feb 2005 23:41] Omer Barnir 
Worlog #1218 includes the following:
   ON <table name>:   
      :
   -- The table and the trigger must be in the same schema/database.  
      :
so based on the above, this is a bug.
[25 Feb 2005 21:13] Sergei Golubchik 
let's think about it first.
I would not call this behaviour a bug.
[1 Jun 2005 19:25] Brian Aker 
We will fix this in 5.1
[3 Jul 2005 18:42] Antony Curtis 
See Bug#5892
[19 Jul 2005 20:39] Dmitry Lenev 
Fixed in 5.0.10
[20 Jul 2005 15:45] Mike Hillyer 
Documented in 5.0.10 changelog.