Wednesday, November 19, 2008

ตัวอย่าง Triggers SQLServer

How to Create After Triggers

# Triggers : คือ เมื่อเราต้องการจะ update บาง table เมื่อเกิดเหตุการกับบาง table

--pattern

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here

END


  1. Working with INSERT Triggers

    INSERT INTO Customers
    VALUES (‘Mayank’,’Gupta’,’Hauz Khas’,’Delhi’,’Delhi’,’110016’,’01126853138’)
    INSERT INTO Customers
    VALUES(‘Himanshu’,’Khatri’,’ShahjahanMahal ’,’Jaipur’,’Rajesthan’,’326541’,’9412658745’)
    INSERT INTO Customers
    VALUES (‘Sarfaraz’,’Khan’,’Green Market’,’Hydrabad’,’AP’,’698542’,’9865478521’)

    INSERT INTO Products
    VALUES (‘ASP.Net Microsoft Press’,550)
    INSERT INTO Products
    VALUES (‘ASP.Net Wrox Publication’,435)
    INSERT INTO Products
    VALUES (‘ASP.Net Unleased’,320)
    INSERT INTO Products
    VALUES (‘ASP.Net aPress’,450)

    CREATE TRIGGER invUpdate ON [Orders]
    FOR INSERT
    AS
    UPDATE p SET p.instock=[p.instock – i.qty]
    FROM products p JOIN inserted I ON p.prodid = i.prodid

    You created an INSERT trigger that referenced the logical inserted table. Whenever you insert a new record in the orders table now, the corresponding record in the products table will be updated to subtract the quantity of the order from the quantity on hand in the instack coloumn of the products table.

  2. Working with DELETE Triggers

    DELETE triggers are used for restricting the data that your users can remove from a database. For example

    CREATE TRIGGER DelhiDel ON [Customers]
    FOR DELETE
    AS
    IF (SELECT state FROM deleted) = ‘Delhi’
    BEGIN
    PRINT ‘Can not remove customers from Delhi’
    PRINT ‘Transaction has been canceled’
    ROOLBACK
    END


    DELETE trigger used the logical deleted table to make certain that you were not trying to delete a customer from the great state “Delhi” – if you did try to delete such a customer, you would be met with Mayank in the form of an error message (which was generated by the PRINT statement that you entered in the trigger code).

  3. Working with UPDATE Triggers

    UPDATE triggers are used to restrict UPDATE statement issued by your users, or to back your previous data.

    CREATE TRIGGER CheckStock ON [Products]
    FOR UPDATE
    AS
    IF (SELECT InStock FROM inserted) <>You created an UPDATE trigger that references the inserted table to verify that you are not trying to insert a value that is less than zero. You need to check only the inserted table because SQL Server performs any necessary mathematical functions before inserting your data.

Note
  • ด้านบนเป็น trigger แบบ after
  • แต่ถ้าเราต้องการให้ trigger ทำงานก่อน insert, update , delete เราต้องใช้ instead of แทน for หรือแทน after นั่นเอง
  • เช่น instead of insert, update, delete
  • table ชั่วคราวได้แก่ inserted, deleted ซึ่งใน trigger ก่อนหรือหลัง query จะมีผลต่อ table เหล่านี้ทั้งสิ้น
  • ส่วน update คือ ก่อน update จะเก็บไว้ที่ deleted หลัง update จะเก็บไว้ที่ inserted
  • เราสามารถใส่ rollback tran ได้เช่นกัน
  • instead of จะไม่สามารถสร้างบน table ที่มี foreign key ได้
  • และ instead of จะเป็นการทำงานแทน insert , update, delete เลย ไม่เหมือน after หรือ for ที่จะทำงานใน trigger หลัง insert, update, delete

References :

No comments:

Post a Comment

Popular Posts