Trigger Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2012-08-03 : 21:33:03

Hi Guys,

I need help. I want to create a Trigger on Table, if someone wants to delete that table Message Popup or enter Password to delete this table. Please advise.

Thank You.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-03 : 22:31:43

Triggers do not work that way. You'll have to write a user interface to accomplish that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 22:34:49

can you explain what you mean by this?

if someone wants to delete that table Message Popup or enter Password to delete this table
do you mean dropping table itself or deleting data? if former, what you need is DDL trigger and if latter DML trigger ON DELETE action. If latter, is it for entire data deletion or any data deletion attempted?

also whom should message be displayed to? the person trying to initiate the action?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-08-04 : 00:03:57

Thanks guys, What i am trying to do. If someone by mistake drop table from Production Table then trigger applied to ask enter Password to drop this table. Its make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-04 : 00:05:27

quote:
Originally posted by tooba

Thanks guys, What i am trying to do. If someone by mistake drop table from Production Table then trigger applied to ask enter Password to drop this table. Its make sense?

what password? where do you set it up?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-08-04 : 00:06:38

Password, that some users know, Not all users..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-04 : 00:23:48

then you've to implement custom security model i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-08-04 : 00:28:15

CREATE TRIGGER DoNotDropTables
ON DATABASE
FOR DROP_TABLE
AS
RAISERROR ('Cannot drop tables!', 10, 1);
ROLLBACK;

GO

Something Like this. When i try to Drop any table from Table i am receiving a error (Cannot drop tables!). Here i want if it is possible instead of RAISE ERROR I WANT TO ASK ENTER PASSWORD, IF I ENTER THE RIGHT PASSWORD I CAN DELETE THAT TABLE. (Possible or not)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-04 : 07:11:15

The best approach is to DENY that permission to all logins except one. If someone wants to drop a table they have to log in to SQL Server with that one login. Then there's no need for a trigger and no way for someone to accidentally drop a table using their login.

And regularly dropping tables on a production server is a bad practice, it makes it too easy for accidents. Hopefully you're making regular backups, because it will happen someday.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources