1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
CREATE SEQUENCE T1_M_SEQ
MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 NOCYCLE ;
CREATE OR REPLACE TRIGGER T1_M_TRG
BEFORE INSERT OR UPDATE ON T1_M
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.ID_M IS NULL THEN
SELECT T1_M_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT max(ID_M) INTO v_newVal FROM T1_M;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT T1_M_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
-- save this to emulate @@identity
sqlserver_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.ID_M := v_newVal;
END IF;
END; |
