Can someone show me an example of an auto increment field in sql when creating a table? Also if possible an auto timestamp?
Thanks
Jamie
Thanks
Jamie
create table jamielib/customer
(cust# smallint not null
generated always as identity
(start with 1
increment by 1
cycle),
shipped_to varchar (5),
name varchar (50),
address varchar (40),
city varchar(18),
state char (2),
constraint customer_cust#
Primary key (Cust#))
CREATE TABLE ORDERS
(ORDERNO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 500
INCREMENT BY 1
CYCLE),
SHIPPED_TO VARCHAR (36) ,
ORDER_DATE DATE)
ALTER TABLE ORDER
ALTER COLUMN ORDERNO
RESTART WITH 1
ALTER TABLE ORDER
ALTER COLUMN ORDERNO
DROP IDENTITY
CREATE TABLE ORDERS
(ORDERNO ROWID
GENERATED ALWAYS,
SHIPPED_TO VARCHAR (36) ,
ORDER_DATE DATE )
CREATE TABLE
MYLIB.TEST
(ENTDATE TIMESTAMP ,
TEST CHAR ( 30) NOT NULL WITH DEFAULT)
CREATE TRIGGER MYLIB.INSERT_DATE
BEFORE INSERT ON MYLIB.TEST
REFERENCING NEW as NEWROW
FOR EACH ROW
BEGIN
SET NEWROW.ENTDATE = CURRENT_TIMESTAMP;
END
INSERT INTO MYLIB.TEST (TEST) VALUES('AAAAAA')
INSERT INTO MYLIB.TEST (TEST) VALUES('BBBBBB')
INSERT INTO MYLIB.TEST (TEST) VALUES('CCCCC')
SELECT * FROM MYLIB.TEST
ENTDATE TEST
2006-04-24-22.11.06.404966 AAAAA
2006-04-24-22.11.12.169658 BBBBB
2006-04-24-22.11.17.439606 CCCCC
******** End of data ********
Comment