USE AdventureWorks IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL DROP TRIGGER Sales.reminder1 GO CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT, UPDATE AS RAISERROR ('Notify Customer Relations', 16, 10) GO
USE AdventureWorks IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL DROP TRIGGER Sales.reminder2 GO CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT, UPDATE, DELETE AS EXEC master..xp_sendmail 'MaryM', 'Don''t forget to print a report for the sales force.' GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL DROP TRIGGER Purchasing.LowCredit GO CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS DECLARE @creditrating tinyint, @vendorid int SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID FROM Purchasing.PurchaseOrderHeader p INNER JOIN inserted i ON p.PurchaseOrderID = i.PurchaseOrderID JOIN Purchasing.Vendor v on v.VendorID = i.VendorID IF @creditrating = 5 BEGIN RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1) ROLLBACK TRANSACTION END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~定义存储过程及应用~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ if exists(select name from sysobjects where name='pro_name' and type='p') //**判断是否存在一个名为pro_name的存储过程 drop procedure pro_name //**如果有这么一个同名的存储过程就删除掉,存储过程不能重名go create procedure pro_name as select column_name from table_name //创建基于某一个表的存储过程 go exec pro_name //调用存储过程 这是一个比较简单的存储过程的定义及使用,还有更为复杂的,如有需要,可继续发帖,慢慢学习 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~定义触发器及应用~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~if exists(select name from sysobjects where name ='trigger_name' and type='tr')//*凑数是否存在一个名为trigger_name的触发器 drop trigger trigger_name //*触发器也不能同名 go create trigger trigger_name on table_name //*创建触发器 for insert,update as raiserror(error_name,error_class) //*当执行插入或更新时会弹出错误信息,raiserror即是弹出错误 信息 go
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
DROP TRIGGER Sales.reminder1
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10)
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the sales force.'
GO
DROP TRIGGER Purchasing.LowCredit
GO
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
@vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader p INNER JOIN inserted i ON p.PurchaseOrderID =
i.PurchaseOrderID JOIN Purchasing.Vendor v on v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1)
ROLLBACK TRANSACTION
END
if exists(select name from sysobjects
where name='pro_name' and type='p') //**判断是否存在一个名为pro_name的存储过程
drop procedure pro_name //**如果有这么一个同名的存储过程就删除掉,存储过程不能重名go
create procedure pro_name
as
select column_name from table_name //创建基于某一个表的存储过程
go
exec pro_name //调用存储过程
这是一个比较简单的存储过程的定义及使用,还有更为复杂的,如有需要,可继续发帖,慢慢学习
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~定义触发器及应用~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~if exists(select name from sysobjects
where name ='trigger_name' and type='tr')//*凑数是否存在一个名为trigger_name的触发器
drop trigger trigger_name //*触发器也不能同名
go
create trigger trigger_name on table_name //*创建触发器
for insert,update
as raiserror(error_name,error_class) //*当执行插入或更新时会弹出错误信息,raiserror即是弹出错误
信息
go