Ajax.Asp.Net Tips and Tricks: Adding Dynamic Controls to UpdatePanel and Identifying what control has triggered the event.

Here is a cool technique on how to identify which control has triggered the UpdatePanel (when dynamic controls are added during runtime). Here we will use the Request Object to identify this.

Lets say we have updatePanel1

<div>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="conditional">
  <ContentTemplate>
    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    <asp:Panel ID="Panel1" runat="server" Height="50px" Width="125px">
    </asp:Panel>
  </ContentTemplate>
</asp:UpdatePanel>
</div>

and during runtime we go and add dynamic buttons.... button1, button2 in the Panel1....

LinkButton button1 = new LinkButton();
button1.ID = "button1"
button1.Text = "button1"

LinkButton button2 = new LinkButton();
button2.ID = "button2"
button2.Text = "button2"

Panel1.Controls.Add(button1);
Panel1.Controls.Add(button2);

now we can identify which button is clicked, by looking at the Request Object and passing the key of the ScriptManager.ID:

protected override void CreateChildControls()
{
  base.CreateChildControls();
  if( ScriptManager1.IsInAsyncPostBack )
  {
    string fromWhere = Request[ScriptManager1.ID];
    Label1.Text = fromWhere;
  }
}
 

This will get the following wtring:
On Button1 click: UpdatePanel1|button1
On Button2 click: UpdatePanel1|button2

if we look carefully at the string  "UpdatePanel1|button1" we see that it has 2 parts, the ID of the UpdatePanel which got triggered and the ID of the control (ie. button1) which triggered the event.

So we can do something like this.....

protected override void CreateChildControls()
{
  base.CreateChildControls();
  if( ScriptManager1.IsInAsyncPostBack )
  {
    string fromWhere = Request[ScriptManager1.ID];   

    if( fromWhere.Contains("button1") )
    {
        //Do something here
    }
    else if (fromWhere.Contains("button2")
    {

        //Do something else....as you wish....
    }
  }
}

DDL Triggers in SQL Server 2005

Triggers are not new to SQL Server. But prior to SQL Server 2005 triggers were DML triggers, which were raised only when there is an INSERT,UPDATE or DELETE action. A new table, database or user being created raises a DDL event and to monitor those, DDL triggers were introduced in SQL Server 2005.

Implementation
Following is the syntax for DDL triggers.

CREATE TRIGGER trigger_name
ON { ALL SERVER DATABASE }
[ WITH [ ,...n ] ]
{ FOR AFTER } { event_type event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] EXTERNAL NAME <> [ ; ] }


DDL triggers can be created in either in the Database or the Server. If you want to monitor table creations and drops, you should create DDL trigger on the database, while to monitor operations like database creations you should create a DDL trigger on the Server.
Take a simple example of creating a database.

CREATE DATABASE [DDL_TRIGGERS_DB]
Let us assume that we want to log all the new table creations. We will log all the events in some other database called DDL_Trigger_Log in a table which has following schema.
CREATE TABLE [dbo].[tblDDLEventLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [varchar](15) NULL,
[ServerName] [varchar](25) NULL,
[DatabaseName] [varchar](25) NULL,
[ObjectType] [varchar](25) NULL,
[ObjectName] [varchar](25) NULL,
[UserName] [varchar](15) NULL,
[CommandText] [varchar](max) NULL,)

Then we need to create a DDL trigger so that all the relevent event data is updated in the above table. Follwing will be the DDL trigger.
CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG] ON DATABASE -- Create Database DDL Trigger
FOR CREATE_TABLE -- Trigger will raise when creating a Table
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML
-- Capture the event data that is created
SET @xmlEventData = eventdata()
-- Insert information to a EventLog table
INSERT INTO DDL_Trigger_Log.dbo.tblDDLEventLog
(
EventTime,
EventType,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
)
SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),
'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
GO
Then create a table and retrieve data in the tblDDLEvetnLog table:
You can see that all the necessary information, we will look more details about DDL triggers.

Database Triggers
As specified before, DDL triggers are executed whenever you create, drop or alter an object at the database level. Users, tables, stored procedures,views, service broker objects like queues , functions and schemas are the objects which fall into the database objects.
In a DDL trigger you can specify the trigger options (ie the operations that need to be triggered). In the above example, it is specified to execute the triggers when a new table is created. However, rather than specify each operation, there are DDL event groups that you can specify. In that case the trigger will be executed for all the operations in that event group. For example, if you specified DDL_DATABASE_LEVEL_EVENTS instead of CREATE_TABLE all the events for CREATE_TABLE, ALTER_TALBE and DROP_TABLE that trigger will be executed hence all the events will be logged.
That trigger will look like below;
CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
/* Your code goes here */
At the end of the article, you will find the all the existing trigger events with it's highrachy. If you specificed an event, the trigger will be excuted for all the subsequent events.
EVENTDATA is an important function in DDL triggers. The EVENTDATA() function will be raised whenever a DDL trigger is fired. Output of the EVETNDATA() function is in XML format. The following is the XML format of the EVENTDATA() with example.
You can use above tags to suit your requirments.
Let us see what are the options that we can use with EVENTDATE() functions.
Apart from monitoring table creations. another requirment for DBAs is to prevent users creating tables or any other objects which does not conform to a standard. For example, if you want to stop users from creating tables which do not have prefix tbl, you can use following DDL trigger.
CREATE TRIGGER [ddltrg_CheckCreateTable] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)'))
IF LEFT(@tableName, 3) <> 'tbl'
BEGIN
RAISERROR ( 'You cannot create table name without starting with tbl',
16,- 1 )
ROLLBACK
END
GO

After creating above DDL trigger, if you try create a table like the following,
CREATE TABLE Customer
(
ID INT,
Desccription VARCHAR(50)
)
You will get below error and table will not be created because of the ROLLBACK statement specified in the trigger.

Msg 50000, Level 16, State 1, Procedure ddltrg_, Line 17
You cannot create table name without starting with tbl
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

It is important to remember is that unlike DML triggers, in DDL triggers you won't find INSTEAD OF triggers. Instead of using INSTEAD OF triggers, you can write the trigger so that it triggers instead of the opreration. Because of this, in DML triggers you do not have to roll them back. As there is no such an option for DDL triggers, you have insert a ROLLBACK which might be a bit expensive.
You can extend the DDL trigger to include stored procedures , functions and for schemas.
Also, if you want to stop users doing ALTER_TABLE during peak hours, you can do this by using the PostTime XML tag of EVENTDATA().

Server Triggers
Server DDL triggers fire when server operations are performed. For example, if you want to audit create database operations, the following trigger can be used.
CREATE TRIGGER [ddlsvrtrg_CREATE_DATABASE_LOG] ON ALL SERVER
FOR CREATE_DATABASE
AS
/* Your code goes here */
This trigger will also have the same EVENTDATA() function with same output XML format. Hence you will have all the options that database triggers have.

Enable or Disable Triggers
As in DML triggers, you have the option to Enable or Disable DDL triggers (for both server and database triggers)
DISABLE TRIGGER ddltrg_CREATE_TABLE_LOG
ON ALL SERVER
GO
ENABLE TRIGGER ddltrg_CREATE_TABLE_LOG
ON ALL SERVER
GO
Trigger Execution Order
When there are several triggers, you can define which trigger to execute first and last. There is a system stored procedure named sp_settriggerorder to set the priority. This is the same stored procedure which you can use to set priority for DML triggers as well.
sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'
, [ @order = ] 'value'
, [ @stmttype = ] 'statement_type'
[ , [ @namespace = ] { 'DATABASE' 'SERVER' NULL } ]
From @order parameter you can set either first or last, which is the order of the trigger execution. The @namespace parameter can be set either DATABASE or SERVER depending on whether the DDL trigger is a database or server dependent trigger.

System Tables
It is often necessary to know where the triggers are saved. In case of database DDL triggers, the information is stored in sys.triggers and sys.trigger_events. The sys.triggers view contains information like trigger name, create date etc and sys.trigger_events view contains the for which events those triggers are going to execute.
SELECT *
FROM sys.triggers
SELECT *
FROM sys.trigger_events

In case of Server DDL triggers, you have to use sys.server_triggers and sys.server_trigger_events.
SELECT *
FROM sys.server_triggers
SELECT *
FROM sys.server_trigger_events

Improvements

Eventhough there are 100+ events included for DDL triggers, there are few important events. Specifically events for database backup, database restore, and SQL Server Job related.