.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

DDL Database Trigger

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :Sql Server

I have a DDL Trigger 'FOR DDL_DATABASE_LEVEL_EVENTS'  that works great for all users that are "sysadmin", but does not fire for new users that have limited security.  I thought that a DDL trigger fired on an event and did not look at the users.  I have provided the DDL Trigger below.....

Thank you in advance


/* This will install a dll trigger on every database except tempdb */

       ,@v_MAX INT
       ,@v_db  SYSNAME
       ,@v_SQL NVARCHAR(MAX)

  col1   INT IDENTITY (1,1),
  dbname SYSNAME

INSERT @t_db
  FROM master.dbo.sysdatabases (NOLOCK)
 WHERE name <> 'tempdb'
 ORDER BY name

SELECT @v_MIN = MIN(col1)
      ,@v_MAX = MAX(col1)
  FROM @t_db

WHILE @v_MIN <= @v_Max

  SELECT @v_db = dbname
    FROM @t_db
   WHERE col1 = @v_MIN
  'EXEC ['

View Complete Post

More Related Resource Links

execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

Database trigger & permission

I created a DDL trigger to keep checking on who alter, create views/tables in a database. Almost most of the update are doing by poweruser. When I try to grant execute right to them.  SQL: GRANT EXECUTE ON [trgLogDDLEvent] to #ABC_Admin It prompts error: GCannot find the object 'trgLogDDLEvent', because it does not exist or you do not have permission. however, when I find the trigger by SQL. It does exist. What is the problem? select * from sys.triggers  

A USE database statement is not allowed in a procedure, function or trigger.




I’m automating a large Archiving\Purging script on 1TB multi-databases.


I run all Sp’s from Archive database (Destination database).


The log file gets full fast in both source and destination databases and the whole process stopped, I have to clear all log files, as well I have to shrink file after clearing it.


help: user "sa" cannot make DDL changes after I turned on DDL auditing via database trigger


I created a database trigger to track DDL changes to SQL 2008 database:



I have already created the AuditDB database and DDLAudit table:

   CREATE TABLE dbo.DDLAudit ...


Will a ROLLBACK in a subscriber database trigger cause the replication engine to abort?


We have some data retention policy deletes for a table in the publisher database that we do not want to be replicated to the subscriber database.  Our method of implementation is to write a delete trigger for the table in the subscriber database that intercepts the deletes by checking the date associated with the deleted data, and if the date is < 2 months ago we execute a ROLLBACK in the trigger.  The ROLBACK is effective and we get the desired results, but this however produces the message:

    Msg 3609, Level 16, State 1, Line 2 

    The transaction ended in the trigger. The batch has been aborted.

Is this message a problem for replication?  Will the replicaton engine abort?  Is this just an informational message, a warning message, or error message?

create database trigger to capture new session users



I would like to capture the session user names of people connecting to our database and store them in a separate table.

I was hoping I could "create trigger foo on database for logon". But that is only valid for server based triggers. The problem is I know adding a server based trigger might upset some of our database guys so ideally I would like to avoid this option if possible.

Does anyone have any suggestions?

Thanks in advance



Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

MS SQL Server: Disconnect Users From Database - Kill User Session

If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.

Publishing SQL Server Database using Publishing Wizard : Tips & Tricks

We can use SQL Server Publishing Wizard to deploy our local Database to remote hosting server/production server.This feature is available in SQL Server 2008/VWD 2008.

Below is the complete step by step guide of this process.

Adding Rows in GridView without using a Database

This article describes on how to add rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in the TextBox on Button Click and retain the GridView data on post back.

Adding Multiple Columns and Rows in GridView without using a Database

This article describes on how to add multiple columns and rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in each TextBoxes on Button Click and retain the GridView data on post back.

ASP.NET Database Tutorial

This tutorial will show you how to display data using the .NET GridView Control, ASP.NET 2.0 and VB.NET

The GridView control is a powerful tool and is simple to implement.

First, you will need to import the System.Data.SqlClient namespace.

Connestion string create any database server

This links important to how to create connection string to any data base sever.

SQL Server Database Transaction

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

Database Export Wizard for ASP.net and SQL Server

A step wizard for ASP.net to export database objects to CSV, TXT, HTML, XML, or SQL. it guides users through a few simple steps to choose a database object (table, view, or query), select columns, and export the data in any of the standard formats CSV, HTML, XML, or SQL.

Reading and Writing Images From a Windows Mobile Database using UltraLite 10(C#)

Periodically I get a request for information on how to read and write binary data to a database running on Windows Mobile. If you search the Internet you can typically find examples that are available on Windows Desktops or allow you to read and write to a local file system. The problem is that it can take a bit of work to get this code to work on Windows Mobile accessing a database.

Ultimately you might be asking, why would I want to store and image in a database? Well in an environment where you synchronizing data between a local mobile database and a consolidated (central) database this can be extremely useful. Imagine if an insurance adjuster went to an accident scene, took a picture of a damaged car, loaded it into his Windows Mobile database and then replicated that image up to the Insurance headquarters for approval. All of this could be done in a very short period of time when using images in the database. Another good example might be a doctor who was waiting for a patient chart to become available. If you could store the image in a database this chart could be sent down to the doctor's device once it became available.

For this article I am not going to get into how to synchronize the images to and from a remote and central database as this is typically fairly straightforward when using a data synchronization technologies like MobiLink
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend