Change detection in MySQL.

**bakuryu** · 18-05-2009, 10:47 PM

Quote:

I have seen the IDENTITY property in MSSQL used like:
CREATE TABLE test(idencol INT IDENTITY(1,1), dupcol INT NOT NULL);

I need to create a same query in MySQL. I saw that there is AUTO_INCREMENT in MySQL. But not sure it is the one which I can use instead if identity.

Can you please confirm me or suggest me if I am wrong.
What can I used instead of IDENTITY?

Yes, you need to use AUTO_INCREMENT

Code:

CREATE TABLE test
(
idencol INT NOT NULL AUTO_INCREMENT, 
dupcol INT NOT NULL,
PRIMARY KEY (idencol)
);

For the trigger, what exactly do you want to achieve with the trigger ?

amit_27 · 19-05-2009, 11:32 AM

Thanks!

I am creating trigger for before insert, update, delete on table.

amit_27 · 19-05-2009, 02:24 PM

Hi,

I have created a trigger which is giving me an error
"ERROR 1415 (0A000): Not allowed to return a result set from a trigger":

CREATE TABLE t1(idencol INT AUTO_INCREMENT,DUMMYCOL INT NOT NULL, PRIMARY KEY(CLIDENTCOL));
GO;
CREATE TRIGGER tri1 BEFORE INSERT ON t2
FOR EACH ROW
BEGIN
DECLARE PN INT;
DECLARE TAB_COL VARCHAR(10);
DECLARE done INT DEFAULT 0;
DECLARE TRG_CURSOR CURSOR FOR SELECT FNAME FROM INSERTED;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN TRG_CURSOR;

REPEAT
FETCH TRG_CURSOR INTO TAB_COL;
IF NOT done THEN
BEGIN
DELETE FROM amit.t1 WHERE DUMMYCOL = 0;
INSERT INTO amit.t1 (DUMMYCOL) VALUES(0);
SELECT @PN = @@IDENTITY;
INSERT INTO amit.CLtable VALUES (@PN, 14);
INSERT INTO amit.CLMtable VALUES (TAB_COL, @PN, 'I');
END;
END IF;
UNTIL done END REPEAT;

CLOSE TRG_CURSOR;
END
$

I have seen the meaning of the error, however there is no specific code found in my trigger which return resultset or code causing problem for this error?

Can you please shed some light on this?

amit_27 · 19-05-2009, 02:33 PM

ok. I got the solution for problem. Hence I solved the problem.

amit_27 · 19-05-2009, 06:04 PM

Is there any default table MySQL Server automatically creates and manages these tables like

INSERTED, DELETED table in MSSQL

DUAL table in ORACLE.

**bakuryu** · 19-05-2009, 08:14 PM

Check information_schema database

MySQL :: MySQL 5.0 Reference Manual :: 19 INFORMATION_SCHEMA Tables

what i meant is what is the purpose of the trigger, what do you want to do before something is inserted ? From the code it seems you are checking for some duplicate values and then taking specific actions.

amit_27 · 20-05-2009, 04:48 PM

Trigger is to implement in such a way that it will have to read the informarmation from the default table created internally by MySQL like in MSSQL creates INSERTED, DELETED tables.
Using the inserted and deleted Tables

Suppose in case of deletion I want the deleted row information, then I will not have the information after deleting that row. So I need to keep track of this information before into tables so can be viewed anytime by query on my own table.

I don't have to use timestamp or autoincrement while creating tables.

I need to know that MySQL creates any virtual tables which stores the information as temporary.

**bakuryu** · 20-05-2009, 08:09 PM

Quote:

Suppose in case of deletion I want the deleted row information, then I will not have the information after deleting that row.

You can use it using Foreign keys.

Code:

CREATE TABLE parent 
(id INT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child 
(id INT AUTO_INCREMENT, 
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;

This will delete rows in child table automatically when referenced parent is deleted.
While inserting to know the value of auto increment coloumn for last insert use : LAST_INSERT_ID() function or in php : PHP: mysql_insert_id - Manual (for php)

otherwise you can use Replication : MySQL :: MySQL 5.0 Reference Manual :: 16 Replication

However you can create views with MySQL also.
http://dev.mysql.com/doc/refman/5.0/en/create-view.html

amit_27 · 26-05-2009, 04:32 PM

What is the default schema name in MySQL 5.1.34 like in ORACLE is username (system), in MSSQL is "dbo"?

I think it is database name Isn't it?

**bakuryu** · 26-05-2009, 05:35 PM

in MySQL it's called information_schema
MySQL :: MySQL 5.0 Reference Manual :: 19 INFORMATION_SCHEMA Tables

19-05-2009, 11:32 AM	#12
amit_27 Junior Member (25+) Join Date: Jan 2009 Posts: 48 Thanks: 13 Thanked 0 Times in 0 Posts Rep Power: 0 OS:	Re: Change detection in MySQL. Thanks! I am creating trigger for before insert, update, delete on table.

19-05-2009, 02:24 PM	#13
amit_27 Junior Member (25+) Join Date: Jan 2009 Posts: 48 Thanks: 13 Thanked 0 Times in 0 Posts Rep Power: 0 OS:	Re: Change detection in MySQL. Hi, I have created a trigger which is giving me an error "ERROR 1415 (0A000): Not allowed to return a result set from a trigger": CREATE TABLE t1(idencol INT AUTO_INCREMENT,DUMMYCOL INT NOT NULL, PRIMARY KEY(CLIDENTCOL)); GO; CREATE TRIGGER tri1 BEFORE INSERT ON t2 FOR EACH ROW BEGIN DECLARE PN INT; DECLARE TAB_COL VARCHAR(10); DECLARE done INT DEFAULT 0; DECLARE TRG_CURSOR CURSOR FOR SELECT FNAME FROM INSERTED; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN TRG_CURSOR; REPEAT FETCH TRG_CURSOR INTO TAB_COL; IF NOT done THEN BEGIN DELETE FROM amit.t1 WHERE DUMMYCOL = 0; INSERT INTO amit.t1 (DUMMYCOL) VALUES(0); SELECT @PN = @@IDENTITY; INSERT INTO amit.CLtable VALUES (@PN, 14); INSERT INTO amit.CLMtable VALUES (TAB_COL, @PN, 'I'); END; END IF; UNTIL done END REPEAT; CLOSE TRG_CURSOR; END $ I have seen the meaning of the error, however there is no specific code found in my trigger which return resultset or code causing problem for this error? Can you please shed some light on this?

19-05-2009, 02:33 PM	#14
amit_27 Junior Member (25+) Join Date: Jan 2009 Posts: 48 Thanks: 13 Thanked 0 Times in 0 Posts Rep Power: 0 OS:	Re: Change detection in MySQL. ok. I got the solution for problem. Hence I solved the problem.

19-05-2009, 06:04 PM	#15
amit_27 Junior Member (25+) Join Date: Jan 2009 Posts: 48 Thanks: 13 Thanked 0 Times in 0 Posts Rep Power: 0 OS:	Re: Change detection in MySQL. Is there any default table MySQL Server automatically creates and manages these tables like INSERTED, DELETED table in MSSQL DUAL table in ORACLE.

19-05-2009, 08:14 PM	#16
bakuryu ƒ(ψ)=Θº×φ Join Date: May 2006 Location: India Age: 23 Posts: 6,621 Thanks: 19 Thanked 645 Times in 603 Posts Rep Power: 87 OS:	Re: Change detection in MySQL. Check information_schema database MySQL :: MySQL 5.0 Reference Manual :: 19 INFORMATION_SCHEMA Tables what i meant is what is the purpose of the trigger, what do you want to do before something is inserted ? From the code it seems you are checking for some duplicate values and then taking specific actions.

20-05-2009, 04:48 PM	#17
amit_27 Junior Member (25+) Join Date: Jan 2009 Posts: 48 Thanks: 13 Thanked 0 Times in 0 Posts Rep Power: 0 OS:	Re: Change detection in MySQL. Trigger is to implement in such a way that it will have to read the informarmation from the default table created internally by MySQL like in MSSQL creates INSERTED, DELETED tables. Using the inserted and deleted Tables Suppose in case of deletion I want the deleted row information, then I will not have the information after deleting that row. So I need to keep track of this information before into tables so can be viewed anytime by query on my own table. I don't have to use timestamp or autoincrement while creating tables. I need to know that MySQL creates any virtual tables which stores the information as temporary.

26-05-2009, 04:32 PM	#19
amit_27 Junior Member (25+) Join Date: Jan 2009 Posts: 48 Thanks: 13 Thanked 0 Times in 0 Posts Rep Power: 0 OS:	Re: Change detection in MySQL. What is the default schema name in MySQL 5.1.34 like in ORACLE is username (system), in MSSQL is "dbo"? I think it is database name Isn't it?

26-05-2009, 05:35 PM	#20
bakuryu ƒ(ψ)=Θº×φ Join Date: May 2006 Location: India Age: 23 Posts: 6,621 Thanks: 19 Thanked 645 Times in 603 Posts Rep Power: 87 OS:	Re: Change detection in MySQL. in MySQL it's called information_schema MySQL :: MySQL 5.0 Reference Manual :: 19 INFORMATION_SCHEMA Tables

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

New To Site?	Need Help?
Register to Participate Meet our Staff Refer Forum Rules Contact Us	Frequently Asked Questions Did you forget your password? Mark Forums Read

Change detection in MySQL.

Programming