SQL server与Oracle触发器的创建与使用

GO
BEGIN
IF (object_id(‘WMY‘, ‘tr‘) is not null)
DROP trigger WMY
END;
GO
CREATE TRIGGER WMY
ON Student
Instead of INSERT
AS
BEGIN
    INSERT INTO Student (number,name) VALUES (1205,‘角色2‘);
END;
--Begin 与end相当于{},是一个语句块,可有可无此处为演示所用
--ON 后面跟表明，表示作用于那个表
--ON Student下面(Instead of INSERT)可有改为(Instead of,after,for)(update,delete,Insert)

GO
INSERT INTO Student (number,name) VALUES (1807,‘角色‘);

USE [OSMP]
BEGIN
IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘Person‘)
AND NOT EXISTS (select * from Person) 
DROP table Person
END
GO
CREATE TABLE Person
(
    num,int
    S_score,int
    S_name,NVARCHAR(64),
    primary key (num)
)
BEGIN
IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘Student‘)
AND NOT EXISTS (select * from Student) 
DROP table Student
END
GO
CREATE TABLE Student
(
    score,int
    name,NVARCHAR(64),
    primary key (name)
)
--创建序列
BEGIN
IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘Student_SEQ‘)
DROP SEQUENCE Student_SEQ
END
CREATE SEQUENCE Student_SEQ
MINVALUE 1
MAXVALUE 999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

GO
BEGIN
IF (object_id(‘WMY‘, ‘tr‘) is not null)
DROP trigger WMY
END;
GO
CREATE TRIGGER WMY
ON Student
instead of INSERT
AS
BEGIN
    Insert into Person (num,S_score,S_name)select next value for Student_SEQ,score,name from Inserted 
    --此处并无意思主要用于理解,执行下面的Student插入语句是，数据库维护了一个与Student数据结构一样的表Inserted,
    --这里利用这个表和序列值来为Person做插入，而实际上没有执行Student插入，如果想要Student也执行把Instead of 改为for或after
END;
GO
Insert into Student(score,name) values(‘1‘,‘m‘);

GO
SELECT current_value FROM sys.sequences WHERE name = ‘Student_SEQ‘

BEGIN
    EXECUTE IMMEDIATE ‘DROP TABLE Person‘;
    EXCEPTION WHEN OTHERS THEN NULL;
END;

CREATE TABLE Person
(
    num        INTEGER,
    S_score   INTEGER,
    S_name     NVARCHAR2(64),
    primary key (num)
)
BEGIN
    EXECUTE IMMEDIATE ‘DROP TABLE Student‘;
    EXCEPTION WHEN OTHERS THEN NULL;
END;

CREATE TABLE Student
(
    score   INTEGER,
    name     NVARCHAR2(64),
    primary key (name)
)
--创建序列
BEGIN
    EXECUTE IMMEDIATE ‘DROP SEQUENCE Student_SEQ‘;
    EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE SEQUENCE Student_SEQ
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

CREATE OR REPLACE TRIGGER TR_INST_DEVICE
BEFORE INSERT ON M_DEVICEENTITY
FOR EACH ROW

BEGIN
    select Student_SEQ.NEXTVAL into :new.num from dual;
    --select ‘A‘ || trim(to_char(:new.num, ‘00000000‘)) into :new.score from dual;
    --这个两语句作用并无练习，第一个取出序列的下一个值插入new表中(new表类似SQL server 中的Inserted)
    --更改score的标示发以A_0000001为样式，这个语句只做参考在此处无用也运行不了(因为表的字段类型）
END;

GO
 Insert into Student(score,name) values(‘1‘,‘m‘)

Insert into Person(n, num, name) select next value for ENTITY_SEQ,number,name from Student where number=114;
--next value for ENTITY_SEQ序列与select字段组合添加
Insert into Person(n, num, name) select cast(100 as int),number,name from Student where number=114;
--cast(100 as int)值与字段组合添加