MBR IT/.NET 247 : Prevent a row from being deleted on ASPFriends.com 'ngfx-sqlclient' list

Prevent a row from being deleted

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.

dmrader@SSEinc.com

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim <Click here to reveal e-mail address> --

I am trying to write a trigger on SQL2K that will examine the values of a
row that is attempting to be deleted and raise an error (and halt the
delete) if the value of column A = "FOO"

Being the newbie to triggers I am, I have absolutely no idea how to do this.
Heres what I have so far:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS
    IF /* Column A == "FOO" */
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END

I am not sure how I evaluate the data though, or if I should be using
INSTEAD OF DELETE...

Can anyone point me in the right direction...

Thanks!

Devin

Reply to this message...

Jose Fuentes

Well for one you would be better off using.
Rules in SQL server
Sort of like triggers but designed for what you want to do.
Check it out.

ClassicFS
Joe Fuentes
Systems Analyst

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 2:18 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Prevent a row from being deleted

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

I am trying to write a trigger on SQL2K that will examine the values of a
row that is attempting to be deleted and raise an error (and halt the
delete) if the value of column A = "FOO"

Being the newbie to triggers I am, I have absolutely no idea how to do this.
Heres what I have so far:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS
    IF /* Column A == "FOO" */
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END

I am not sure how I evaluate the data though, or if I should be using
INSTEAD OF DELETE...

Can anyone point me in the right direction...

Thanks!

Devin
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

David L. Penton

Consider the virtual DELETED table:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS

IF EXISTS(SELECT 1 FROM deleted WHERE ColA = 'Foo')
BEGIN
RAISERROR('Cannot remove users from ''All Users'' group.',16,1)
END

David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

I am trying to write a trigger on SQL2K that will examine the values of a
row that is attempting to be deleted and raise an error (and halt the
delete) if the value of column A = "FOO"

Being the newbie to triggers I am, I have absolutely no idea how to do this.
Heres what I have so far:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS
    IF /* Column A == "FOO" */
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END

I am not sure how I evaluate the data though, or if I should be using
INSTEAD OF DELETE...

Can anyone point me in the right direction...

Thanks!

Devin

Reply to this message...

dmrader@SSEinc.com

Not familiar with Rules, can you show an example?

Devin

-----Original Message-----
From: Jose Fuentes [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 3:05 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Prevent a row from being deleted

Well for one you would be better off using.
Rules in SQL server
Sort of like triggers but designed for what you want to do. Check it out.

ClassicFS
Joe Fuentes
Systems Analyst

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 2:18 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Prevent a row from being deleted

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

I am trying to write a trigger on SQL2K that will examine the values of a
row that is attempting to be deleted and raise an error (and halt the
delete) if the value of column A = "FOO"

Being the newbie to triggers I am, I have absolutely no idea how to do this.
Heres what I have so far:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS
    IF /* Column A == "FOO" */
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END

I am not sure how I evaluate the data though, or if I should be using
INSTEAD OF DELETE...

Can anyone point me in the right direction...

Thanks!

Devin
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

dmrader@SSEinc.com

OK David, heres what I got so far:

ALTER TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
INSTEAD OF DELETE
AS

    DECLARE @GroupName varchar(50);
    SELECT @GroupName = dbo.Groups.GroupName FROM dbo.Groups, deleted
WHERE dbo.Groups.GroupId = deleted.GroupId;

    IF @GroupName = 'All Users'
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END
    ELSE
        BEGIN
            DELETE FROM dbo.GroupMembership, deleted WHERE
dbo.GroupMembership.GroupId = deleted.GroupId;
        END

I get an error on line 15, the line with the DELETE statement. The way I
understood the BOL documentation a INSTEAD OF DELETE first populates the
deleted table, but does not actually perform the delete, therefore you have
to manually do it in your trigger code.

So, that being said, whats wrong with the DELETE statement, can't I specify
two tables like I am above?

Devin

-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 3:13 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Prevent a row from being deleted

Consider the virtual DELETED table:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS

IF EXISTS(SELECT 1 FROM deleted WHERE ColA = 'Foo')
BEGIN
RAISERROR('Cannot remove users from ''All Users'' group.',16,1) END

David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the Soul. -
J.S. Bach" Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

I am trying to write a trigger on SQL2K that will examine the values of a
row that is attempting to be deleted and raise an error (and halt the
delete) if the value of column A = "FOO"

Being the newbie to triggers I am, I have absolutely no idea how to do this.
Heres what I have so far:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS
    IF /* Column A == "FOO" */
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END

I am not sure how I evaluate the data though, or if I should be using
INSTEAD OF DELETE...

Can anyone point me in the right direction...

Thanks!

Devin

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

David L. Penton

Best to use aliases:

DELETE a
FROM
dbo.GroupMembership a
INNER JOIN
deleted b
ON
a.GroupId = b.GroupId

David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]

OK David, heres what I got so far:

ALTER TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
INSTEAD OF DELETE
AS

    DECLARE @GroupName varchar(50);
    SELECT @GroupName = dbo.Groups.GroupName FROM dbo.Groups, deleted
WHERE dbo.Groups.GroupId = deleted.GroupId;

    IF @GroupName = 'All Users'
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END
    ELSE
        BEGIN
            DELETE FROM dbo.GroupMembership, deleted WHERE
dbo.GroupMembership.GroupId = deleted.GroupId;
        END

I get an error on line 15, the line with the DELETE statement. The way I
understood the BOL documentation a INSTEAD OF DELETE first populates the
deleted table, but does not actually perform the delete, therefore you have
to manually do it in your trigger code.

So, that being said, whats wrong with the DELETE statement, can't I specify
two tables like I am above?

Devin

-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 3:13 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Prevent a row from being deleted

Consider the virtual DELETED table:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS

IF EXISTS(SELECT 1 FROM deleted WHERE ColA = 'Foo')
BEGIN
RAISERROR('Cannot remove users from ''All Users'' group.',16,1) END

David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the Soul. -
J.S. Bach" Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

I am trying to write a trigger on SQL2K that will examine the values of a
row that is attempting to be deleted and raise an error (and halt the
delete) if the value of column A = "FOO"

Being the newbie to triggers I am, I have absolutely no idea how to do this.
Heres what I have so far:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS
    IF /* Column A == "FOO" */
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END

I am not sure how I evaluate the data though, or if I should be using
INSTEAD OF DELETE...

Can anyone point me in the right direction...

Thanks!

Devin

Reply to this message...

Mike Campbell

Devin,

One thing that you may not have gleaned from BOL is that your trigger
will be called each time there is a DELETE statement fired against the
table. Note that it WON'T be fired for every row that is about to be
deleted.

So.. Let's say some moron decided to run the following against your
table:
DELETE FROM Groups

(notice that there is no WHERE clause).

If that statement were fired against your table, the trigger would fire
ONCE. That's why David Keeps using JOINS against the deleted table, and
treating the deleted table like it is a BUNCH of rows, instead of like
it is just one row. That's why his EXISTS clause works... He's looking
to see if that field is in the virtual table of rows that are about to
be deleted.

In the case of the code he suggested... Your entire would be saved (an
nice side effect) because within that table you'd find (hopefully) one
instance of the stuff you were trying to block people from deleting.

Anyhow, I hope this clarifies a bit for you. Forgive me if you already
knew this.

--Mike

-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 3:28 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Prevent a row from being deleted

Best to use aliases:

DELETE a
FROM
dbo.GroupMembership a
INNER JOIN
deleted b
ON
a.GroupId = b.GroupId

David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]

OK David, heres what I got so far:

ALTER TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
INSTEAD OF DELETE
AS

    DECLARE @GroupName varchar(50);
    SELECT @GroupName = dbo.Groups.GroupName FROM dbo.Groups,
deleted
WHERE dbo.Groups.GroupId = deleted.GroupId;

    IF @GroupName = 'All Users'
        BEGIN
            RAISERROR('Cannot remove users from ''All
Users''
group.',16,1)
        END
    ELSE
        BEGIN
            DELETE FROM dbo.GroupMembership, deleted WHERE
dbo.GroupMembership.GroupId = deleted.GroupId;
        END

I get an error on line 15, the line with the DELETE statement. The way
I
understood the BOL documentation a INSTEAD OF DELETE first populates the
deleted table, but does not actually perform the delete, therefore you
have
to manually do it in your trigger code.

So, that being said, whats wrong with the DELETE statement, can't I
specify
two tables like I am above?

Devin

-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 3:13 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Prevent a row from being deleted

Consider the virtual DELETED table:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS

IF EXISTS(SELECT 1 FROM deleted WHERE ColA = 'Foo')
BEGIN
RAISERROR('Cannot remove users from ''All Users'' group.',16,1) END

David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. -
J.S. Bach" Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

I am trying to write a trigger on SQL2K that will examine the values of
a
row that is attempting to be deleted and raise an error (and halt the
delete) if the value of column A = "FOO"

Being the newbie to triggers I am, I have absolutely no idea how to do
this.
Heres what I have so far:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS
    IF /* Column A == "FOO" */
        BEGIN
            RAISERROR('Cannot remove users from ''All
Users''
group.',16,1)
        END

I am not sure how I evaluate the data though, or if I should be using
INSTEAD OF DELETE...

Can anyone point me in the right direction...

Thanks!

Devin

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT
-

-

Reply to this message...

Jose Fuentes

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
create2_1lnp.asp

ClassicFS
Joe Fuentes
Systems Analyst

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 4:23 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Prevent a row from being deleted

Not familiar with Rules, can you show an example?

Devin

-----Original Message-----
From: Jose Fuentes [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 3:05 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Prevent a row from being deleted

Well for one you would be better off using.
Rules in SQL server
Sort of like triggers but designed for what you want to do. Check it out.

ClassicFS
Joe Fuentes
Systems Analyst

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 2:18 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Prevent a row from being deleted

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

I am trying to write a trigger on SQL2K that will examine the values of a
row that is attempting to be deleted and raise an error (and halt the
delete) if the value of column A = "FOO"

Being the newbie to triggers I am, I have absolutely no idea how to do this.
Heres what I have so far:

CREATE TRIGGER GroupMembership_PreventAllUsersDeletion
ON dbo.GroupMembership
FOR DELETE
AS
    IF /* Column A == "FOO" */
        BEGIN
            RAISERROR('Cannot remove users from ''All Users''
group.',16,1)
        END

I am not sure how I evaluate the data though, or if I should be using
INSTEAD OF DELETE...

Can anyone point me in the right direction...

Thanks!

Devin
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com