4.22sql学习

---触发器
USE NorthWind
IF EXISTS(SELECT name FROM sysobjects WHERE name='tr_product_update' AND type='TR')
DROP TRIGGER tr_product_update
GO
USE NorthWind
GO
CREATE TRIGGER tr_product_update ON products
FOR UPDATE
AS
DECLARE @msg varchar(100)
SELECT @msg=str(@@rowcount)+'employees updated by this statement'
RETURN
GO

--inserted表和deleted表
USE NorthWind
GO
CREATE TRIGGER tr_suppliers_del
ON suppliers
FOR DELETE
AS
IF @@rowcount=0
RETURN
DELETE products
FROM deleted d,supliers s
WHERE d.supplierid=s.supplierid
IF @@error!=0
BEGIN
ROLLBACK tran
RETURN
END
RETURN
GO

 

---UPDATE(column):判断指定的某列是否经过了修改。
USE NorthWind
GO
--建立登记修改人帐号的表
CREATE TABLE who_change(change_date datetime,
                        change_column varchar(50),
                        who           varchar(50)
)
GO
--建立触发器
CREATE TRIGGER tr_orderdetail_insupd
ON [order details]
FOR INSERT,UPDATE
AS
IF UPDATE(UnitPrice)
BEGIN
INSERT who_change
VALUES (getdate(),'UnitPrice updated',user_name())
END
ELSE IF UPDATE(Quantity)
BEGIN
INSERT who_change
VALUES(getdate(),'Quantity updated',user_name())
END
ELSE IF UPDATE(Discount)
BEGIN
INSERT who_change
VALUES(getdate(),'Discount updated',user_name())
END
GO