restrict the delete recorde in database or datatable by using trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » restrict the delete recorde in database or...

restrict the delete recorde in database or datatable by using trigger

Rate Topic

Display Mode

Topic Options

Author

Message

dastagirid

Posted Thursday, November 19, 2009 5:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 02, 2009 12:19 AM
Points: 108, Visits: 248

hi

It delete record when a trigger fire to ristrict it.

How to write trigger .

plz help me .

Thanks
Dastagiri.D

Post #821519

Dugi

Posted Thursday, November 19, 2009 5:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 1,123, Visits: 2,573

can you post more info about your problem ...!? Better to answer with more information if we have..anyway check BOL!

http://msdn.microsoft.com/en-us/library/aa258254%28SQL.80%29.aspx

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #821522

dastagirid

Posted Thursday, November 19, 2009 6:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 02, 2009 12:19 AM
Points: 108, Visits: 248

Sorry for this..

I have 10 records in a table.

Now i am going to delete one record in a table.

When i have to use trigger to restrict the delete record.

Thanks
Dastagiri.D

Post #821526

Nabha

Posted Thursday, November 19, 2009 6:23 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:54 AM
Points: 546, Visits: 1,650

Go through the link provided by Dugi. You will get what you want!

I think you need Instead of trigger. A trascript from the BOL

INSTEAD OF

Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.

Its there in that same link.

---------------------------------------------------------------------------------

Post #821540

Jack Corbett

Posted Thursday, November 19, 2009 6:29 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:56 AM
Points: 8,186, Visits: 7,969

I still don't understand exactly what you are asking. Do you want to know how to restrict users from deleting a specific row from a table?

Here's an article that introduces triggers that may help you.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #821544

Dugi

Posted Thursday, November 19, 2009 7:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 1,123, Visits: 2,573

dastagirid (11/19/2009)

Sorry for this..

I have 10 records in a table.

Now i am going to delete one record in a table.

When i have to use trigger to restrict the delete record.

OK try something like this:

CREATE TRIGGER TRIGGER_NAME
ON YOUR_TABLE
FOR DELETE
AS
INSERT INTO SOME_TABLE_FOR_AUDIT
SELECT * FROM DELETED

But what you need to do is that you should create the SOME_TABLE_FOR_AUDIT with the same columns with YOUR_TABLE like this:

SELECT * INTO SOME_TABLE_FOR_AUDIT
FROM YOUR_TABLE;
GO

TRUNCATE TABLE SOME_TABLE_FOR_AUDIT;
GO

Change the Identity Insert if your table has Identity check ON!

That's it for the moment and hope it works as you wish!!!

Let us know for any other problem!?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #821592

Dugi

Posted Thursday, November 19, 2009 7:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 1,123, Visits: 2,573

Sorry this is for you problem:

CREATE TRIGGER CANNOT_DELETE
ON YOURTABLE
FOR DELETE
AS
BEGIN 
RAISERROR ('DELETIONS ARE NOT ALLOWED!!!',16,1)
ROLLBACK TRANSACTION; 
END

The code above is for the auditing the deletions!

Anyway you have 2 alternatives to audit and to prevent the deletions of the data!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #821604

dastagirid

Posted Thursday, November 19, 2009 9:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 02, 2009 12:19 AM
Points: 108, Visits: 248

Thanks alot...................

Exactly This is for I asked.

Thanks
Dastagiri.D

Post #822065

Dugi

Posted Friday, November 20, 2009 10:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 1,123, Visits: 2,573

dastagirid (11/19/2009)

Thanks alot...................

Exactly This is for I asked.

You are welcome!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #822560

« Prev Topic | Next Topic »

Permissions