sql server解发器例子

--    Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
--            Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

CREATE TABLE  ent
(
id INT IDENTITY(1,1),
percess     DECIMAL(5,2) DEFAULT 0.00,
t_mony DECIMAL(5,2),
t_count DECIMAL(4,2)
)
GO
CREATE TABLE  ent_log
(
id INT IDENTITY(1,1),
idd VARCHAR(20)
)
GO

INSERT INTO ent(t_mony, t_count)VALUES(11.11,0.11);
INSERT INTO ent(t_mony, t_count)VALUES(22.11,0.11);
INSERT INTO ent(t_mony, t_count)VALUES(24.11,0.11);
SELECT * FROM ent;

 SELECT * FROM sysobjects WHERE
        type = 'TR'



create trigger entinsert on ent
FOR insert as
UPDATE ent SET percess =inserted.t_mony*inserted.t_count FROM INSERTED  , ent t WHERE INSERTED.id= t.id
 GO
 
 INSERT INTO ent(t_mony, t_count)VALUES(100.0,1.120);
SELECT * FROM ent;
DELETE FROM ent WHERE id=3
 ---测试完成
-- DROP TABLE ent ;
-- DROP TRIGGER entinsert
 --DROP TRIGGER[Battery_Updated]
--DROP TRIGGER tri_Topic_Delete
 CREATE TRIGGER [dbo].[Battery_Updated]
   ON  [dbo].ent
   INSTEAD OF UPDATE
AS
UPDATE ent SET percess =-inserted.t_mony*inserted.t_count FROM INSERTED  , ent t WHERE INSERTED.id= t.id
GO
----测试
UPDATE ent
SET
    t_mony = 100.22
WHERE id=2
SELECT * FROM ent;
-----


CREATE TRIGGER dbo.tri_Topic_Delete ON ent
FOR DELETE
AS
BEGIN
   
    DECLARE @key VARCHAR(20)    
    SELECT @key = t_mony+'22' FROM deleted
   INSERT INTO ent_log
   (
   
       idd
   )
   VALUES
   (
 @key
   )
END

---测试
DELETE FROM ent WHERE id=5;
SELECT
    id,
    percess,
    t_mony,
    t_count
FROM
    ent
    
    
    SELECT
*
FROM
    ent_log

转载于:https://www.cnblogs.com/window5549-accp/archive/2011/07/20/2111738.html