query metadata within trigger scope

I have a few tables which I'd like to protect from accidental deletes.
I'm thinking adding an INSTEAD of trigger on DELETE to the table that
raise an error and return could be an approach (not sure if its the
best). Something like:
CREATE TRIGGER trig_DeleteNoAllowed
ON sandbox.dbo.Table1
INSTEAD OF DELETE
AS
BEGIN
if @.@.ROWCOUNT > 0
BEGIN
RAISERROR ('Deletion not allowed.', 16, 1)
RETURN
END
END
However, I'd like to tell the user which table he tries to delete rows
from. Something like:
BEGIN
if @.@.ROWCOUNT > 0
BEGIN
DECLARE @.tablename varchar(128)
SET @.tablename = [?]
RAISERROR
('Deletion not allowed on table %d.',
16, 1, @.tablename)
RETURN
END
END
How do I get the name of the table on which the trigger is working!
MortenHi
Take a look at this example
CREATE TABLE TT
(
COL INT
)
CREATE TRIGGER MY_TR ON TT
FOR INSERT
AS
DECLARE @.ObjID int
SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
INSERT INTO TT VALUES (1)
SELECT * FROM TT
DROP TABLE TT
"wapsiii" <wapsiii@.otmail.com> wrote in message
news:6po8o1t2s5fsd06uo862o86vcc5nhm95q4@.
4ax.com...
>I have a few tables which I'd like to protect from accidental deletes.
> I'm thinking adding an INSTEAD of trigger on DELETE to the table that
> raise an error and return could be an approach (not sure if its the
> best). Something like:
> CREATE TRIGGER trig_DeleteNoAllowed
> ON sandbox.dbo.Table1
> INSTEAD OF DELETE
> AS
> BEGIN
> if @.@.ROWCOUNT > 0
> BEGIN
> RAISERROR ('Deletion not allowed.', 16, 1)
> RETURN
> END
> END
> However, I'd like to tell the user which table he tries to delete rows
> from. Something like:
> BEGIN
> if @.@.ROWCOUNT > 0
> BEGIN
> DECLARE @.tablename varchar(128)
> SET @.tablename = [?]
> RAISERROR
> ('Deletion not allowed on table %d.',
> 16, 1, @.tablename)
> RETURN
> END
> END
> How do I get the name of the table on which the trigger is working!
> Morten|||Try something like this:
BEGIN
if @.@.ROWCOUNT > 0
BEGIN
DECLARE @.tablename varchar(128)
SELECT @.tablename = so2.name
FROM sysobjects so1
INNER JOIN sysobjects so2
ON so1.id = so2.parent_obj
AND so2.xtype = 'tr'
AND so2.id= @.@.PROCID
RAISERROR
('Deletion not allowed on table %d.',
16, 1, @.tablename)
RETURN
END
END
Please remember that certain tables will change to the next release of
SQL server (assuming that you are using SQL2k here)
HTH, jens Suessmeyer.|||Since the trigger code needs to specify the table in the CREATE TRIGGER
... part, why not just include the table name instead of trying to
parameterize it?
Any reason why you want to use a trigger rather than security to
prevent accidental deletes:
DENY DELETE ON dbo.Table1 TO ...
This has the advantage that you can apply it selectively and you won't
block deletes by admins. A trigegr on the other hand is hard to disable
selectively.
David Portas
SQL Server MVP
--|||thanks for your replies...
I was thinking... even if I use the table as a primary key table and
hence have delete constraints on used values, I'd like not even admins
to be able to delete un-used values by accident. I couldn't come with
any other way than using a trigger or instead of trigger. Of course
the trigger could easily be disabled, but then it would hardly be an
accident.
I reached the same conclusion in regards to "just include the table
name and not trying to parameterize it". I guess this too would be
faster. However, I learned something, picking around in metadata :)
On 23 Nov 2005 05:25:35 -0800, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>Since the trigger code needs to specify the table in the CREATE TRIGGER
>... part, why not just include the table name instead of trying to
>parameterize it?
>Any reason why you want to use a trigger rather than security to
>prevent accidental deletes:
>DENY DELETE ON dbo.Table1 TO ...
>This has the advantage that you can apply it selectively and you won't
>block deletes by admins. A trigegr on the other hand is hard to disable
>selectively.
>--
>David Portas
>SQL Server MVP