sql server: create Trigger using del,insert,update

/*


create database geovindu;
两数据库之间的一个类似表，进行一个表的操作添加，修改时的同步操作的触发器操作

use geovindu;

create database DuMap
go

use DuMap
go
-- Geovin Du,涂聚文
--Geovin Du， 塗聚文
*/

--基础数据设置BasicDataSet
--书分类目录kind
--BookKindForm
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].BookKindList') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE BookKindList
GO
create table BookKindList
(
    BookKindID INT IDENTITY(1,1) PRIMARY KEY,
    BookKindName nvarchar(500) not null,
    BookKindParent int null,
    BookKindCode varchar(100)   ---編號
)
GO
 
alter table BookKindList add BookKindCode varchar(100)
 
select * from BookKindList
 
insert into BookKindList(BookKindName,BookKindParent,BookKindCode) values('六福书目录',0,'00')
 
insert into BookKindList(BookKindName,BookKindParent,BookKindCode) values('文学',1,'100')
insert into BookKindList(BookKindName,BookKindParent,BookKindCode) values('设计艺术',1,'101')
insert into BookKindList(BookKindName,BookKindParent,BookKindCode) values('自然科学',1,'102')
 
insert into BookKindList(BookKindName,BookKindParent,BookKindCode) values('小说',2,'201')
insert into BookKindList(BookKindName,BookKindParent,BookKindCode) values('诗词散曲',2,'202')

insert into BookKindList(BookKindName,BookKindParent,BookKindCode) values('geovindu',2,'203')

insert into BookKindList(BookKindName,BookKindParent,BookKindCode) values('公文3',2,'205')

---添加触发器 Geovin Du, geovindu,涂聚文，塗聚文
drop TRIGGER InsertTriggerBookKind
go

use geovindu; --在geovindu数据库中修改时，相关DuMap，自动的修改或删除，添加，所以数据相同，包括主键值一样

SELECT CONVERT(decimal(8,2),'2088.9') --2088.90


if exists(select * from sysobjects where name='InsertTriggerBookKind')
drop trigger InsertTriggerBookKind;
go

CREATE TRIGGER InsertTriggerBookKind
ON dbo.BookKindList
for INSERT  --AFTER
AS
BEGIN
DECLARE
@BookKindID int,
@BookKindName nvarchar(500),
@BookKindParent int,
@BookKindCode varchar(30)
--SET NOCOUNT ON;
SELECT @BookKindID =ins.BookKindID,
@BookKindName= ins.BookKindName,
@BookKindParent= ins.BookKindParent,
@BookKindCode=ins.BookKindCode FROM INSERTED as ins;
insert into DuMap.dbo.BookKindList(BookKindName,BookKindParent,BookKindCode) values(@BookKindName,@BookKindParent,@BookKindCode); 
PRINT(cast(@BookKindID as varchar)+','+@BookKindName+','+cast(@BookKindParent as varchar)+','+ @BookKindCode)
END

--判断是否存在触发器，如果存在则删除
if exists(select * from sysobjects where name='inserttri')
drop trigger inserttri;
go

if exists(select * from sysobjects where name='UpdateTriggerBookKind')
drop trigger UpdateTriggerBookKind;
go
CREATE TRIGGER UpdateTriggerBookKind
ON dbo.BookKindList
for UPDATE  --AFTER [DELETE] [,] [INSERT] [,] [UPDATE] 
AS
BEGIN
DECLARE
@BookKindID int,
@BookKindName nvarchar(500),
@BookKindParent int,
@BookKindCode varchar(30),
@OldBookKindID int,
@OldBookKindName nvarchar(500),
@OldBookKindParent int,
@OldBookKindCode varchar(30);
-- update触发器是当用户在指定表上执行update语句时被调用被调用，这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作：更新前的记录存储在deleted表中，更新后的记录存储在inserted表中。
SELECT @OldBookKindID =olds.BookKindID,
@OldBookKindName= olds.BookKindName,
@OldBookKindParent= olds.BookKindParent,
@OldBookKindCode=olds.BookKindCode FROM deleted as olds;
--
SELECT @BookKindID =ins.BookKindID,
@BookKindName= ins.BookKindName,
@BookKindParent= ins.BookKindParent,
@BookKindCode=ins.BookKindCode FROM INSERTED as ins;
--Edit
update DuMap.dbo.BookKindList set  BookKindName=@BookKindName,BookKindParent=@BookKindParent,BookKindCode=@BookKindCode
where BookKindID=@OldBookKindID --and BookKindName=@OldBookKindName and BookKindCode=@OldBookKindCode
--
PRINT(cast(@BookKindID as varchar)+','+@BookKindName+','+cast(@BookKindParent as varchar)+','+ @BookKindCode)
END
 

 if exists(select * from sysobjects where name='DeletTriggerBookKind')
drop trigger DeletTriggerBookKind;
go
CREATE TRIGGER DeletTriggerBookKind
ON dbo.BookKindList
for delete  --AFTER [DELETE] [,] [INSERT] [,] [UPDATE] 
AS
BEGIN
DECLARE
@BookKindID int,
@BookKindName nvarchar(500),
@BookKindParent int,
@BookKindCode varchar(30)
SELECT @BookKindID =ins.BookKindID,
@BookKindName= ins.BookKindName,
@BookKindParent= ins.BookKindParent,
@BookKindCode=ins.BookKindCode FROM deleted as ins;
delete DuMap.dbo.BookKindList where BookKindID=@BookKindID --and BookKindName=@BookKindName and BookKindCode=@BookKindCode; --如果ID一样，用ID
PRINT(cast(@BookKindID as varchar)+','+@BookKindName+','+cast(@BookKindParent as varchar)+','+ @BookKindCode)
END

 --select @id=sno from inserted

 select * from BookKindList

 delete BookKindList where BookKindID=8
 go

 update BookKindList set BookKindName='公文' where BookKindID=7
 go