Before Update/Delete Trigger

Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, JeremyRead about INSTEAD OF triggers in BOL|||Hi JCScoobyRS,

Originally posted by JCScoobyRS
Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, Jeremy

h, why don't you revoke the user the UPDATE and DELETE permission?|||Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy|||Originally posted by JCScoobyRS
Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy

In this case you should go for INSTEAD OF triggers|||Okay...that sounds good. Here is an example of what I need to do:

I'm trying to prevent the UPDATE and DELETE on a table after a certain field has been entered(not null). Here is the trigger right now:

CREATE TRIGGER TRANSACTION_REPORTEE ON [dbo].[TRAVAUX_COMMANDE]
FOR UPDATE, DELETE
AS
IF [dbo].[TRAVAUX_COMMANDE].[Id_Transaction_GL] IS NOT NULL
BEGIN
RAISERROR ('Impossible de modifier une ligne reporte',10,1)
ROLLBACK TRAN
END

This is what my buddy has. Is there anyway to take what he has here and revise it with your idea in it for testing? Thanks alot, Jeremy

Before Delete

I have 2 databases "Law","Rules" .. the second have tables which is linked
to the first one... so i want to deny Deleting of Record from first if it
has a child record in the other database...
I notice that there is no "Before Delete" trigger in sql server so how could
i control deleteing records from first database..
Second.. how could i roll-back Delete or update operation?
Did you consider a Foreign key Constraint for that ? If it is not
applicable you can do a ROLLBACK within a trigger and raise an error to
show up the error to the user.
http://groups.google.de/group/micros...18307e92ac868c
HTH, Jens Suessmeyer.
|||Instead of trying ot use a trigger, how about applying a foreign key
constraint instead? Then when you try to delete a row from the first
table you'll get an error if there's a dependent row in the second
table. It'll also be much faster than using a trigger.
On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
wrote:

>I have 2 databases "Law","Rules" .. the second have tables which is linked
>to the first one... so i want to deny Deleting of Record from first if it
>has a child record in the other database...
>I notice that there is no "Before Delete" trigger in sql server so how could
>i control deleteing records from first database..
>Second.. how could i roll-back Delete or update operation?
>
|||hi,
bradsbulkmail@.comcast.net wrote:[vbcol=seagreen]
> Instead of trying ot use a trigger, how about applying a foreign key
> constraint instead? Then when you try to delete a row from the first
> table you'll get an error if there's a dependent row in the second
> table. It'll also be much faster than using a trigger.
> On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
> wrote:
have you tried something like
SET NOCOUNT ON
CREATE DATABASE a
CREATE DATABASE b
GO
USE a
CREATE TABLE dbo.m (
Id int NOT NULL PRIMARY KEY ,
Descr varchar (10) NOT NULL
)
GO
USE b
GO
CREATE TABLE dbo.d (
ID int NOT NULL PRIMARY KEY ,
IdRif int NOT NULL
CONSTRAINT fk_d_m FOREIGN KEY
REFERENCES a.dbo.m (Id) ,
Descr varchar (10) NOT NULL
)
GO
USE master
GO
DROP DATABASE a
DROP DATABASE b
?
the actual result is
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'a.dbo.m'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply