ELECT S2.[name] TableName, S1.[name] TriggerName,
CASE
WHEN S1.deltrig > 0 THEN 'Delete'
WHEN S1.instrig > 0 THEN 'Insert'
WHEN S1.updtrig > 0 THEN 'Update'
END 'TriggerType'
FROM sysobjects S1 JOIN sysobjects S2
ON S1.parent_obj = S2.[id]
WHERE S1.xtype='TR'
Thanks A lot Sir...but i would like to know how to create a trigger in sql and what is the use of it?? also crazy to know about stored procedures ??
ReplyDeleteWhat is Trigger ?
ReplyDeleteA Trigger is a special type of stored procedure which execute based on some event fired. Triggers are attached with some Tables, Database or Server and registered with some events, when ever that event fire, trigger will execute automatically and do the predefine job. As I have already mention execution of trigger is based on some events, so we cannot run trigger manually.
Trigger can execute on data modification using Insert, Delete, Update command or it can execute some data definition change like Table creation etc. We can also create trigger on server level which will execute based on some event perform on server. Some times trigger can cause execution of other trigger- know as "Nested Trigger"
General Syntax of Creating Trigger is
To summarize the whole things
Triggers are special type of Stored procedure which executed based on some events.
We cannot execute trigger manually.
We can have trigger on Table Level, Database Level and Server Level
We can not use parameter with trigger
we cannot use any return value from Trigger.
Now lets have a look on the advantages and disadvantages of Trigger.
Advantages of Trigger
Trigger has the following advantages
Trigger invoked based on some events.
Trigger can be fired on database and also server event
It is very helpful to check some complex condition where Constraints failed.
It is used for database table auditing
Triggers can be used to enforce constraints on Table.
Disadvantages of Trigger
Triggers that are created on table are invisible, so its very difficult to maintain them.
Triggers run every time when the database fields are updated/Insert/delete and it is overhead on system.
we cannot use parameter with trigger
Trigger can not return any value
Different Types of Trigger
As I have already discuss, trigger can be applicable in Table, Database or in Database Server. Based on that, we can categorize trigger in following way.
Triggers type
SQL Sever command divided into two major category DML ( Data Manipulation Language ) and DDL (Data Definition Language) . DML statement are usually work with Database Table like insert update and Delete. DML trigger are those trigger which are execute when some DML statement fired on a table. DDL statements are used to build and modify the structure of your tables and other objects in the database. In SQL Server 2005 we have another Trigger called CLR Trigger. Now coming back to the details,
DML Trigger : This is based on Table. DML trigger are execute when any of the following DML statement fired on that particular Table. Those are
Insert
Delete
Update
we can attach a DML Trigger with any table against of those (Insert, delete, update ) SQL Command. we can have the DML Trigger for View also. We can create multiple trigger on a single event . In that case we have to specify the sequence of execution. If have discussed it in later.
There are Two types of DML Trigger
AFTER Trigger
INSTEAD OF Trigger
example
ReplyDeletealter trigger Get_comm on tablename after insert,update
as
begin
print 'hello your data is inserted'
end
or
ReplyDeletecreate trigger Get_comm on cadmessage after insert,update
as
begin
print 'hello your data is inserted'
end