触发器
Create table LogInfo ( Id uniqueidentifier unique, content text null, Inputdate datetime not null, ) alter table LogInfo add constraint constraint_Id default newid() for Id alter table LogInfo add constraint constraint_Inputdate default getdate() for Inputdate
if(exists(select 1 from sys.objects where name='update_forbidden')) drop trigger update_forbidden GO create trigger update_forbidden on studentinfo_2019 after update as begin if(update(name)) begin begin try declare @oldname varchar(50),@newname varchar(50),@studentId varchar(50) set @oldname = (select Name from deleted) set @newname = (select Name from inserted) set @studentId = (select studentId from deleted) insert into LogInfo (content) values('学号为 '+@studentId+' 的姓名 由 ' + @oldname +' 改为 '+@newname) end try begin catch rollback transaction end catch end end
if(exists(select 1 from sys.objects where name='delete_forbidden')) drop trigger delete_forbidden GO create trigger delete_forbidden on studentinfo_2019 after delete as begin RAISERROR('禁止直接删除该表数据,操作被禁止',1,1)--raiserror 是用于抛出一个错误 rollback transaction end
inserted 和 deleted 虚拟表 作为表存在 而不是变量