Go4Expert
Page 1 of 3
1
2
3
Show 40 post(s) from this thread on one page

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forumdisplay.php?f=42)
-   -   IS there any MySQL Default tables like DUAL in ORACLE. (http://www.go4expert.com/showthread.php?t=17621)

amit_27 20May2009 13:08
IS there any MySQL Default tables like DUAL in ORACLE.
 
IS there any default table mysql server automatically creates and manages these tables like INSERTED, DELETED table in MSSQL, DUAL table in ORACLE.

I have used in mssql a query in trigger for insert operation
declare trg_cursor cursor for select fname from inserted;

In oracle a query in trigger for insert operation
select system.clsyncseq.nextval into synchpointnumber from dual;

like this i need default tables for mysql to fire a same query.

I have written a trigger on table t1 for insert. When inserted a row to t1 my trigger will read the information from INSERTED, DUAL tables in case of MSSQL, ORACLE instead of table t1.
The same thing I need to write for MySQL for INSERT, UPDATE, DELETE operation.
Can you please direct me to achieve this?

pradeep 20May2009 16:28
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
Code: SQL
declare trg_cursor cursor FOR SELECT fname

amit_27 20May2009 16:54
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
created table account :
Code:
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| acct_num | int(11)      | YES  |    | NULL    |      |
| amount  | decimal(10,2) | YES  |    | NULL    |      |
+----------+---------------+------+-----+---------+-------+
If I use (MSSQL type)
declare trg_cursor cursor for select acct_num;

while insert :
mysql> INSERT INTO account (acct_num, amount) VALUES (0, 23);$
ERROR 1054 (42S22): Unknown column 'acct_num' in 'field list'

pradeep 21May2009 22:02
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
Code: SQL
declare trg_cursor cursor FOR SELECT acct_num;

You have to specify a table name here

amit_27 22May2009 12:53
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
Exactly, I need to specify the table name here. I was asking for the table name to specify in my query like DUAL, INSERTED, DELETED in ORACLE, MSSQL.

Like INSERTED in MSSQL, DUAL table in ORACLE temporary store the updated, inserted, deleted row whilst and I can get the acct_num of only new, delete, updated row form DUAL table for my transaction.

The same thing I want to achieve using MySQL. But I didn't see any table which

pradeep 22May2009 13:33
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
Dual is available in MySQL as well

Code: SQL
SELECT COUNT(*) col FROM dual WHERE 1=0

amit_27 22May2009 13:54
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
Yes, we can use the dual table but not like the Oracle. If we don't have any table reference then we can use dual in MySQL. like for arithmatic operations.

amit_27 26May2009 15:33
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
Thanks pradeep answering my question. I solved the problem.

As MySQL support "For Each Row" mechanisam, we get the directly the output which want using "new" and "old" keyword.

Lke new.acct_num, old.acct_num.

Default table not required and also not available.

amit_27 26May2009 15:34
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
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?

amit_27 27May2009 15:23
Re: IS there any MySQL Default tables like DUAL in ORACLE.
 
what is the default escape character in MySQL?

We have in ORACLE like ' " ', in MSSQL like ' [] ' which is used while creating table.

MSSQL
create table [aa/njh] (col1 INT);

ORACLE
create table "aa/njh" (col1 INT);


If I use both( ", []) in MYSQL while create table it is nor working fine.
My aim is to mhave table name with special character on numeric.


All times are GMT +5.5. The time now is 22:03.
Page 1 of 3
1
2
3
Show 40 post(s) from this thread on one page