Alert when object is added
-
Can anyone pl. give me any idea on how to create and alert for ex. an Email when an object is added to My database? I am interested in alert when Table is added. Trigger won't work and I am not sure how to do this with Alert setting in EM. Try adding new alert through EM but wasn't sure how I set up when table is added.
Thanks.
-
Why does not a trigger work? I would create a trigger on sysobjects, checking for inserted rows with xtype = 'U'.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Have you checked out Schema Alert by Lumigent (http://www.lumigent.com
)? Also you could periodically run the following: against each of your databases
select * from sysobjects where crdate > '2002-10-08'
where you would change the date you are looking for. I this case this shows me all the table that where create TODAY
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
As for triggers on system tables. This is a bad practice and should be avoided.
Note from BOL
quote:
Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.They can have undesired effects such as blocking and may even just fail so therefor were of no use. The best is to do as Greg stated (sorry Chris) and run a periodic query against sysobjects.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
quote:
As for triggers on system tables. This is a bad practice and should be avoided.Yes, you're right. No need to be sorry.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Well, I don't want you to feel I am steping on your toes, just wanted to throw that out there for safety.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
I think Trigger won't work becuse I am looking for Object creation not for DML commands. and I am not sure if that works with Trigger. But I think Greg's query would do it. May be I can add command to emils me the results of this query then this should work.
THanks.
-
changes to the attribute of an existing column in your user table will cause crdate in sysobjects to change
-
An alternative to a trigger would be a job that checked the CRDATE field of sysobjects and notified you. Very flexible and customizable.
Let me know if you want particulars.
Be great!
Michael
Be great!
Michael -
OK, Thanks for response. Here is where I am. I decided to use Greg's query and then send me email query results. I am trying to put together a script which can send me this email. I use Query analyzer and use sa login to run this query in the database that I need results for. And sql server comes up with message that procedure xp_sendmail wasn't found. What am I doing wrong?
Thanks.
-
Hi,
try exec master.dbo.xp_sendmail
regards,
Klaas-Jan
-
Thanks, Klaas-jan
I got it working but now my only question is when I use this query it sends me result of master database and if I don't give master.dbo then I get 'proc. not found.' How do I get it work so that proc. works and it give result of Mydatabase.
Thanks for all your help..
use mydatabase
go
exec master.dbo.xp_sendmail @recipients = 'myself',
@query='select o.name,o.id,o.xtype,o.parent_obj,o.crdate,u.name createdBy
from sysobjects o join sysusers u ON o.uid=u.uid
where o.xtype in (''u'',''v'') and o.crdate > getdate()-7'
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@width = 750
-
Fully qualify the table names in the query (that is always a good thing to do):
use mydatabase
go
exec master.dbo.xp_sendmail @recipients = 'myself',
@query='select o.name,o.id,o.xtype,o.parent_obj,o.crdate,u.name createdBy
from yourDB.dbo.sysobjects o join yourDB.dbo.sysusers u ON o.uid=u.uid
where o.xtype in (''u'',''v'') and o.crdate > getdate()-7'
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@width = 750
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Antares,
I agree with you that it is a bad idea to create triggers on System Tables as per BOL. But is there any other way how we can come to know if an object is deleted or an Index is dropped by someone?
-
You could create a table similar to the sysobjects table (for example tb_dba_object_list).
Load a list of initial object names, types, creation dates, etc. from sysobjects.
Create a SP that would be executed periodically via SQLAgent job (every 5 minutes, 1 hour, whatever) to read the selected objects types (index, table, etc.) from the sysobjects table into a temporary table.
Use the values in the temporary table and compare them to the values in the "tb_dba_object_list" table. This comparison would require using the temporary to update the "tb_dba_object_list" table with the latest object information.
Use triggers on the "tb_dba_object_list" that would fire if an object exists BUT has a different creation date (meaning an objects was dropped and recreated), an object is in the "tb_dba_object_list" table but no longer in the sysobjects table (an object was deleted/renamed), and an objects exists in sysobjects but not in "tb_dba_object_list" (new object or an object renamed).
I actually used a similar system to send automated alerts to users who needed to know when specified SPs were changed/created.
Hope this helps.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply