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

Use of ROLLBACK in SQL server Stored Procedure

Use of ROLLBACK in SQL server Stored Procedure

You might have heard about ROLLBACK but you have never used it right ?

If you have used it, its well and good but if Not, then this blog might help you to know, how you can use ROLLBACK in your SP.

Suppose you want to use two insert statement, one by one.

You want to use, @@identity of first insert in another insert statement then this is the best method to go with.

Suppose you are doing first insert operation and some problem occure and any how you are not able to insert data in second table then there might be some database mapping issue.

so here, ROLLBACK can help you to avoid such database problem,

For example, you have used ROLLBACK in your SP, and you are doing first insert operation and if some problem occur and you are not able to insert in second table then ROLLBACk will undo your first insert operation also which will help to avoid database mapping issue.


Below is the syntax of ROLLBACK,

BEGIN TRY
    BEGIN TRAN
    BEGIN
        'You insert statement
                            
        COMMIT TRAN
                    
    END
END TRY
 
BEGIN CATCH
    BEGIN
        ROLLBACK TRAN
                    
        PRINT ERROR_MESSAGE()
    END
END CATCH

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

Wishing Happy Diwali to all Asp.Net Developers

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

Trick to place jQuery Validation Error

In this post, i will show you, how to place jQuery error where you like.

By default, when you use jquery validation function, then error get placed below the control for which you are validating.

But sometime, situation arise, you want jQuery error to be shown someother place for that control, so in that condition, you can use below trick and place error, where you like.

To do this,

You have to declare jquery files in your Page Head Tag, as shown below.

<script type="text/javascript" src="../resources/js/jQuery/jquery-1.4.3.min.js"></script>
<script type="text/javascript" src="../resources/js/jQuery/jquery.validate.js"></script>

After declareing files, just use below code to validate the control

        <script type="text/javascript">
            $(document).ready(function() {
                $("#frmPEOfficeCreation").validate({
                    rules: {
                        txtdepartment: { required: true }
                    },
                    messages: {
                        txtdepartment: { required: "<br/>Please select department" }
            }
                });
            });
        </script>

You can see below Image as output, where you can see, the error is shown after "new window" button, that is not looking good and not correct with respect to layout.

so Now what you can do is,you can make use of errorplaceholder function avalible in jQuery, LIke i have done below

<script type="text/javascript">
            $(document).ready(function() {
                $("#frmPEOfficeCreation").validate({
                    rules: {
                        txtdepartment: { required: true }
                    },
                    messages: {
                        txtdepartment: { required: "<br/>Please select department" }
            },
                    errorPlacement: function(error, element) {
                        if (element.attr("name") == "txtdepartment")
                            error.insertAfter("#btnnewbutton");
                        else
                            error.insertAfter(element);
                    }
                });
            });
        </script>

Then you can see correct layout, shown below in image

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