1 / 14

Trigger

Trigger. How Important is Backup of your data? How To Take Backup of all the Transaction ? By Copying * Daily Basis * Weekly * Monthly or ……………. Means Manually ???. How about a Automated Process……………….

cara
Download Presentation

Trigger

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Trigger

  2. How Important is Backup of your data? How To Take Backup of all the Transaction ? By Copying * Daily Basis * Weekly * Monthly or …………….

  3. Means Manually ???

  4. How about a Automated Process……………….. • Like the Same “Insert” command which can insert the records in two different tables. • Original Table • Backup Table t1 Insert t1 values (…..) t2 Use TRIGGER To achieve this what we have to do is :

  5. A trigger is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. Microsoft® SQL Server™ allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement.

  6. Insert Insert Command 1 Rows Affected Temporary Table Destroyed Inserted Table Temporary Table Created Inserted table will contain the intermediate value before going into the Table. The Creation and Destroy process is carried out by SQL Server.

  7. Insert stud values (‘alice’,12) Temporary Table Name : Inserted Location : RAM Original Table Name : Student Location : Secondary Storage Temp. Table Destroyed

  8. Delete Delete Command 1 Rows Affected Temporary Table Destroyed Deleted Table Temporary Table Created Deleted table will contain the value that is deleted from the table. The Creation and Destroy process is carried out by SQL Server.

  9. Update will be …… Update Command 1 Rows Affected Temporary Table Destroyed Deleted Table Inserted Table Temporary Table Created Update Command :- The old value will be deleted and new value will be inserted.

  10. Creating Triggers Create Trigger <trigger Name> on <tablename> for <insert/update/delete> AS SQL Statements

  11. Trigger for insert Create trigger trg_ins on student for insert As Select * from inserted Creates a trigger on the table student for insert command which will assign an additional task to the insert command.

  12. Firing trigger Insert student values (‘Alice ’,34) Result :- 1 Row(s) Affected

  13. Backup Create trigger trg_ins on student for insert As declare @n char(20),@r int Select @n=inserted.name,@r=inserted.roll from inserted insert dd_backup values (@n,@r)

  14. Thank You

More Related