开发者

From MySQL to Oracle (NOW() and autoincrement ID)

I really don't know how to ask this, but... the code will help me:

/**
 * Languages table definition
 */
DROP TABLE IF EXISTS `languages`;
CREATE TABLE `languages`
(
    -- Common:
    `id`      INTEGER    NOT NULL AUTO_INCREMENT                COMMENT 'Unique registry identifier',
    `created` TIMESTAMP  NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Registry creation TIMESTAMP',
    `updated` TIMESTAMP  NOT NULL DEFAULT NOW() ON UPDATE NOW() COMMENT 'Registry last update TIMESTAMP',
    `active`  BOOLEAN    NOT NULL DEFAULT TRUE                  COMMENT 'Virtual deletion flag',
    PRIMARY KEY (`id`),
    -- /Common
    开发者_开发知识库`language_id`   INTEGER(2)  UNSIGNED NOT NULL COMMENT 'Language identifier',
    `language_code` VARCHAR(2)           NOT NULL COMMENT 'ISO 639-1 Code',
    `language_name` VARCHAR(20)          NOT NULL COMMENT 'Language name',
    -- Indexes
    UNIQUE KEY `language_id` (`language_id`)
) ENGINE=InnoDB CHARACTER SET 'latin1' COLLATE 'latin1_general_ci';

/**
 * Updates multilang field to UTF-8
 */
ALTER TABLE `languages` MODIFY `language_name` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

/**
 * Assigns the current TIMESTAMP to the created field without user input
 */
CREATE TRIGGER `insert_languages` BEFORE INSERT ON `languages`
    FOR EACH ROW
SET NEW.created = NOW();

/* Insert a record */
INSERT INTO languages(language_id, language_code, language_name) VALUES(10, 'es', 'Español');

/* Update that record (after a few seconds) */
UPDATE languages SET language_name = 'Español (España)' WHERE language_id = 10;

When retrieving * WHERE language_id = 10, two different timestamps are received without explicitly touching the TIMESTAMP fields, also when inserting I don't need to incude an ID. Question is: How do I create the same behavior in Oracle?


  1. You need to use sequences with BEFORE INSERT trigger for having autoincrement id
  2. You need to maintain the dates using sysdate or systimestamp or localdate ... instead of NOW() also in triggers, BEFORE INSERT and BEFORE UPDATE
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜