How to create Trigger in SQL server ?

In this post you can see, how to create trigger in SQL server 2005/2008

ALTER TRIGGER t_XYZ_Update 
   ON  tbl_tableName1
   AFTER UPDATE
AS 
IF ( UPDATE (tableField1) OR UPDATE (tableField2) OR UPDATE (tableField3) OR UPDATE (tableField4))
    BEGIN
    SET NOCOUNT ON;
    
    DELETE FROM tbl_tableName2 WHERE FieldId=(select FieldId from inserted)
    print 'Row Deleted Successfully'
    END
GO

Name you trigger like below line

ALTER TRIGGER t_XYZ_Update

Assign the table name on which you have to perform operation like below line

ON  tbl_tableName1

Design when you want to allow this trigger to get fire, here let take after UPDATE operation.

AFTER UPDATE

If any of the tbl_tableName1 field like tableField1,tableField2,tableField3,tableField4 get updated outside by query or in SP, then this trigger will get fire and perform operation assign to this trigger, you can find when field get updated by below query,

 
IF ( UPDATE (tableField1) OR UPDATE (tableField2) OR UPDATE (tableField3) OR UPDATE (tableField4))

Here in below box, you can see the operation you want to perform when this trigger get fire, here we have used "inserted" table, whenever any of field found updated, same row is inserted in "inserted" table so by this table you can find which row is updated and you can make use of that rowid to perform other operation,

 
    BEGIN
    SET NOCOUNT ON;
    
    DELETE FROM tbl_tableName2 WHERE FieldId=(select FieldId from inserted)
    print 'Row Deleted Successfully'
    END

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
Email: raj143svmit@gmail.com
e-Procurement Technologies Ltd (India)
www.abcprocure.com

Prevent table from drop and re-create during saving changes

Hello Friends,

Problem : Whenever you make any changes in the table defination, when table is fill with data or rows,it does not allow you to delete or make any changes to table.


Solution : You might have faced this issue many times, when ever you make any changes in the table defination and you try to save changes, then you might have seen error messages as shown in below image.

So here is the steps by which you can avoid dropping of table or re-creating it again by using below steps..

Step 1. There is one option in sql server, which allow you to save your changes to table without droping it.

You just click on Tool option in top menu, See below images you can see from where you can go to tool option,

Step 2. Then click on "Option",
then you will be prompt with options Box, in that you will see left menu, in that explore "Designers" option.


After Exploring Designers option, just click on "table and database designer", you will see below screen

Step 3. In below snap, you can see the field in Red Box, just untick it, so that you can save change.

Untick - Prevent saving changes that require table re-creation


Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
Email: raj143svmit@gmail.com
e-Procurement Technologies Ltd (India)
www.abcprocure.com