Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

What is trigger in database management system? Give an example.

user-image
Question added by DURGESH YADAV , ASSISTANT PROGRAMMER , DISTRICT ELECTION OFFICE DEORIA
Date Posted: 2014/08/21

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.

The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.

SET NOCOUNT ON CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10)) go CREATE TRIGGER tr_Source_INSERT ON Source FOR INSERT AS PRINT GETDATE() go INSERT Source (Sou_Desc) VALUES ('Test1')

 

Gayasuddin Mohammed
by Gayasuddin Mohammed , Advocate , Practicing Law before High Court at Hyderabad

Triggers are database objects created on table in a DB.

Basically there are3 types of triggers Insert, Update and Delete kinds of triggers.

these are like small stored procedure or pl/sql blocks gets activate the respective trigger  (i.e insert or update or delete) on the particular table it is created on, to act as per the SP or pl/sql statements.

simply, an example, a field in a table shouldn't have a value of say10, because may be some reason it is only a internal code which cannot be done by 'xyz' user.

in such a scenario i make a update trigger mentioning the code in my trigger block that checking for such update by 'xyz' and if i find it true, then rise a user error code by making update failure, other wise i may have a audit table where I can insert a record mentioning the update made by so and so user in a so and so table and so and so information, may be useful for tracking or audit purpose, if something goes wrong with system. very useful this way. this is only one quickly i can mention here, there is lot more usuage is there with triggers.

some info sharing with the little knowledge i have in this subject and correct me i'm wrong somewhere. thanks

More Questions Like This