November 19, 2009 at 5:56 am
hi
It delete record when a trigger fire to ristrict it.
How to write trigger .
plz help me .
Thanks
Dastagiri.D
November 19, 2009 at 5:59 am
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
November 19, 2009 at 6:05 am
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
November 19, 2009 at 6:23 am
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.
---------------------------------------------------------------------------------
November 19, 2009 at 6:29 am
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[/url] that introduces triggers that may help you.
Jack Corbett
Consultant - Straight Path Solutions
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
November 19, 2009 at 7:22 am
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!?
November 19, 2009 at 7:30 am
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!
November 19, 2009 at 9:01 pm
Thanks alot...................
Exactly This is for I asked.
Thanks
Dastagiri.D
November 20, 2009 at 10:37 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply