SQL Trigger on sys.databases table

Question

0.00/5 (No votes)

See more:

Dear Friends,

i want write trigger on sys.database table in which
one colum name state_desc is change when one of database in suspect/ file recovery mode.

i want event when value of table and colum state_desc change on that event i will perform my other operation.

Posted 22-Feb-15 19:11pm

shoaibmqureshi

Updated 23-Feb-15 2:27am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-02-22T19:18:00

It really isn't advisable to moderate system tables, in any way. Your trigger or any other addition could cause undesired behaviour and whenever a patch or service pack is applied to the SQL Server your modifications may disappear. Because of these reasons modifications to system owned objects are not allowed by default.

If you intend to create a trigger own your own table then you have defined a wrong table in the CREATE statement. Should it be something like:

SQL

CREATE TRIGGER TriggeredSuspect on MyTableNameGoesHere
INSTEAD of Update
AS declare @empname varchar(55), @empAdd varchar(55), @audit_action varchar(100);
select @empname='Suspect'
select @empAdd='Suspect'
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into [TestDB].dbo.employee(emp_name,emp_add)
values (@empname,@empAdd);
PRINT 'AFTER INSERT trigger fired.'