sql进阶-触发器的实现
/* 1.触发器语法: Create Trigger 名称 on 表名 for [insert| update | delete] --三个选一个 as --逻辑语句 go 2. 触发器中存在两个虚拟表,两个虚拟表的表结构跟操作的表的结构一致 分别为inserted 表、 deleted表 */
Create Trigger [dbo].[tri_Student_i]
On [dbo].[Student]
for insert
As
declare @id int
declare @name varchar(50)
declare @age int
select @id=studentid,@name=name ,@age=age from Inserted;
update Student1 set studentid=@id,name=@name,age=@age where name =@name and age =@age
if @@rowcount=0
begin
insert into Student1 values (@id,@name,@age)
end
GO
附上demo表结构:
CREATE TABLE [dbo].[Student]( [StudentID] [int] NOT NULL, [name] [varchar](50) NULL, [age] [int] NULL ) CREATE TABLE [dbo].[Student1]( [StudentID] [int] NOT NULL, [name] [varchar](50) NULL, [age] [int] NULL )