Dot Net Questions And Answers

Name:

[Visual Basic] <%@ control inherits = "MyCodeBehind" src = "UserControl.vb" %>

Name:



5. Include the user control in the Web Forms pages where you want its functionality to appear. 4. In what order do the events ofan ASPX page Execute?

Control Execution Lifecycle The server loads an ASP.NET page every time it is requested and then unloads it after the request is completed. The page and the server controls it contains are responsible for executing the request and rendering HTML back to the client. Although the communication between the client and the server is stateless and disconnected, the client experience must appear to be that of a continuously executing process.

This illusion of continuity is created by the ASP.NET page framework and by the page and its controls. On postback, a control must behave as if it were starting where it left off at the end of the previous Web request. The ASP.NET page framework makes it relatively easy to perform state management, but control developers must be aware of the control execution sequence to achieve the effect of continuity. Control developers need to understand which information is available to a control at each phase in its lifecycle, which data is persisted, and what the control's state is when it is rendered. For example, a control is unable to invoke its parent until the tree of controls on a page has been populated. The following table provides a high-level overview of the phases in the lifecycle of a control. For details, follow the links in the table.

Phase Initialize

What a control needs to do Initialize settings needed during the lifetime of the incoming Web request. Load view state At the end of this phase, the ViewState property of a control is automatically populated as described in Maintaining State in a Control. A control can override the default implementation of the LoadViewState method to customize state restoration. Process postback data Process incoming form data and update properties accordingly. See Processing Postback Data. Note Only controls that process postback data participate in this phase. Load Perform actions common to all requests, such as setting up a database query. At this point, server controls in the tree are created and initialized, the state is restored, and form controls reflect client-side data Send postback change Raise change events in response to state notifications changes between the current and previous postbacks. Note Only controls that raise postback change events participate in this phase. Handle postback Handle the client-side event that caused the events postback and raise appropriate events on the server Note Only controls that process postback events participate in this phase. Prerender Perform any updates before the output is rendered. Any changes made to the state of the control in the prerender phase can be saved, while changes made in the rendering phase are lost

Method or event to override Init event (OnInit method) LoadViewState method

LoadPostData method (if IPostBackDataHandler is implemented)

Load event (OnLoad method)

RaisePostDataChangedEvent method (if IPostBackDataHandler is implemented)

RaisePostBackEvent method (if IPostBackEventHandler is implemented)

PreRender event (OnPreRender method)

Save state

Render Dispose

Unload

The ViewState property of a control is SaveViewState method automatically persisted to a string object after this stage. This string object is sent to the client and back as a hidden variable. For improving efficiency, a control can override the SaveViewState method to modify the ViewState property. Generate output to be rendered to the client. Render method Perform any final cleanup before the control Dispose method is torn down. References to expensive resources such as database connections must be released in this phase. Perform any final cleanup before the control UnLoad event (On UnLoad is torn down. Control authors generally method) perform cleanup in Dispose and do not handle this event.

5. Dataset, Repeater and List control Difference?

Type of Control Table

•

Purpose Generalpurpose, programmable table.

• •

• •

Repeater

•

Simple, readonly output

• •

• •

•

Features Can display any combination of HTML text and controls. Supports a TableCell control that can be set to display information. Does not use templates for display. Is not inherently data-bound. Exposes a model for dynamically creating rows (TableRow controls) and cells (TableCell controls). Has no built-in support for selecting or editing items. Has no default appearance; you must lay out the list by creating templates. The list can be vertical, horizontal, all on one line, or in any format you specify. Has no default paging; all data is displayed in a single list. Can define separators between elements using a template. Supports custom functionality that can be applied to items (for example, specifying an

DataList

• •

•

List output with editing Non-tabular lists (for example, commadelimited lists)

•

Easily customizable output

•

• • • • •

• • •

•

DataGrid

•

Full-featured list output with editing

•

Paged reporting

• • • •

•

• • • • • • •

"Add to shopping cart" button for each item). Has a table appearance by default, but can be configured to show any list output. Can customize look of list extensively. Has an auto-format option. WYSIWYG template editing. Supports styles for custom look of items. Can define separators between elements using a template. Has editable contents (contents displayed in text boxes or other controls, based on data type of bound data). Supports single selection. Multiple selection requires custom code. Supports either horizontal (columns) or vertical (rows) layout of the data items. Has no default paging; all data is displayed in a single list. Supports custom functionality that can be applied to items (for example, specifying an "Add to shopping cart" button for each item). Has a grid appearance by default. Can customize look of grid extensively. Has an auto-format option. Can specify output using bound columns, columns of buttons or hyperlinks, and custom columns created using templates. Has no separator template. However, the grid renders in a table, and you can specify table border size and color. Supports WYSIWYG template editing. Items support styles for custom look. Can edit, update, and delete contents. Supports single selection. Multiple selection requires custom code. Has optional paged output. Supports sorting. Supports custom functionality that can be applied to items (for example, specifying an "Add to shopping cart" button for each item).

6. What method do you use to explicitly kill a user Session?

Ans : Session.Abandon Abandon The Abandon method destroys all the objects stored in a Session object and releases their resources. If you do not call the Abandon method explicitly, the server destroys these objects when the session times out. Syntax

Session.Abandon

Remarks When the Abandon method is called, the current Session object is queued for deletion, but is not actually deleted until all of the script commands on the current page have been processed. This means that you can access variables stored in the Session object on the same page as the call to Abandon, but not in any subsequent Web pages. For example, in the following script, the third line prints the value Mary. This is because the Session object is not destroyed until the server has finished processing the script. <% Session.Abandon Session("MyName") = "Mary" Reponse.Write(Session("MyName")) %>

Contents.Remove The Remove method deletes a specific item from the Session object's Contents collection. Syntax Session.Contents.Remove( Item|Index ) Parameter

Item - The name of the member to remove from the collection. Index - The index entry for the member to remove from the collection. Remarks The Contents.Remove method takes either a string or an integer as an input parameter. If the input parameter is a string, the method will search the contents collection for an item with that name and remove it. If the input parameter is an integer, the method counts that number of items from the start of the collection, and removes the corresponding item. Example The following example adds and removes a variable called myName to the Session.Contents collection.

<% Session("myName") = " " Session.Contents.Remove("myName") %>

Contents.RemoveAll The RemoveAll method deletes all items that have been added to the Session object's Contents collection. Syntax Session.Contents.RemoveAll () Example The following example removes all items that have been added to the Session.contents collection:

<%Session.Contents.RemoveAll()%> 7. Difference between HTML control and Web control and Server control?

Html Control: Browser will maintain this code. This is call client control. Web Control: Same as Html Control. Server control: IIS or Server will maintain this code. 8. Difference Between Global.asax and web.config file? Global.asax: This file maintain the events. Suppose you want initialize a variable in a particular event means you can assign. User can declare global variable in this file. Web.Config: This file maintain the Security measurement. We can connect database globally. This file is fully XML format. 9. Difference between Trace and Debug? Trace: After deployment of our project, admin to trace a error in our project use of trace=true property. If trace is true admin can see the error in the web page. Debug: 10. In ASP.NET we have .aspx file and HTML file which file execute first explain HTML is execute first. Because HTML is browser side code. But .aspx file is fully contain server side code and controls. So that one take time to communicate in the server.

C# 1. what is Boxing and Unboxind. Give Example.

Boxing Boxing is an implicit conversion of a value type to the type object or to any interface type implemented by this value type. Boxing a value of a value allocates an object instance and copies the value into the new object. Consider the following declaration of a value-type variable: int i = 123; The following statement implicitly applies the boxing operation on the variable i: object o = i; The result of this statement is creating an object o, on the stack, that references a value of the type int, on the heap. This value is a copy of the value-type value assigned to the variable i. The difference between the two variables, i and o, is illustrated in the following figure.

It also possible, but never needed, to perform the boxing explicitly as in the following example: int i = 123; object o = (object) i;

Example This example converts an integer variable i to an object o via boxing. Then the value stored in the variable i is changed from 123 to 456. The example shows that the object keeps the original copy of the contents, 123. // boxing.cs // Boxing an integer variable using System; class TestBoxing { public static void Main() { int i = 123;

object o = i; // Implicit boxing i = 456; // Change the contents of i Console.WriteLine("The value-type value = {0}", i); Console.WriteLine("The object-type value = {0}", o); } }

Output The value-type value = 456 The object-type value = 123

Unboxing Unboxing is an explicit conversion from the type object to a value type or from an interface type to a value type that implements the interface. An unboxing operation consists of: • •

Checking the object instance to make sure it is a boxed value of the given value type. Copying the value from the instance into the value-type variable.

The following statements demonstrate both boxing and unboxing operations: int i = 123; object box = i; int j = (int)box;

// A value type // Boxing // Unboxing

The following figure demonstrates the result of the preceding statements.

Unboxing Conversion

For an unboxing conversion to a given value type to succeed at run time, the value of the source argument must be a reference to an object that was previously created by boxing a value of that value type. If the source argument is null or a reference to an incompatible object, an InvalidCastException is thrown.

Example The following example demonstrates a case of invalid unboxing, of how incorrect unboxing leads to InvalidCastException. By using try and catch, an error message is displayed when the error occurs. using System; public class UnboxingTest { public static void Main() { int intI = 123; // Boxing object o = intI; // Reference to incompatible object produces InvalidCastException try { int intJ = (short) o; Console.WriteLine("Unboxing OK."); } catch (InvalidCastException e) { Console.WriteLine("{0} Error: Incorrect unboxing.",e); } } }

Output System.InvalidCastException at UnboxingTest.Main() Error: Incorrect unboxing. If you change the statement: int intJ = (short) o; to: int intJ = (int) o; the conversion will be performed, and you will get the output Unboxing OK.

2. What are the fundamental difference between value type and reference type? C# divides types into two categories - value types and reference types. Most of the basic intrinsic types (e.g. int, char) are value types. Structs are also value types. Reference

types include classes, interfaces, arrays and strings. The basic idea is straightforward - an instance of a value type represents the actual data (stored on the stack), whereas an instance of a reference type represents a pointer or reference to the data (stored on the heap). The most confusing aspect of this for C++ developers is that C# has predetermined which types will be represented as values, and which will be represented as references. A C++ developer expects to take responsibility for this decision. For example, in C++ we can do this: int x1 = 3; // x1 is a value on the stack int *x2 = new int(3) // x2 is a reference to a value on the heap but in C# there is no control: int x1 = 3; int x2 = new int();

x2 = 3;

// x1 is a value on the stack

// x2 is also a value on the stack!

3. Are C# Destructors the same as C++ Destructors? Example. No! They look the same but they're very different. First of all, a C# destructor isn't guaranteed to be called at any particular time. In fact it's not guaranteed to be called at all. Truth be told, a C# destructor is really just a Finalize method in disguise. In particular, it is a Finalize method with a call to the base class Finalize method inserted. So this: class CTest { ~CTest() { System.Console.WriteLine( "Bye bye" ); } } is really this: class CTest { protected override void Finalize() { System.Console.WriteLine( "Bye bye" ); base.Finalize(); } }

With the arrival of Beta 2, explicitly overriding Finalize() like this is not allowed the destructor syntax must be used. 4. Define Delegates? A delegate is a class derived from System.Delegate. However the language has a special syntax for declaring delegates which means that they don't look like classes. A delegate

represents a method with a particular signature. An instance of a delegate represents a method with a particular signature on a particular object (or class in the case of a static method). For example: using System; delegate void Stereotype(); class CAmerican { public void BePatriotic() { Console.WriteLine( "... ... God bless America."); } } class CBrit { public void BeXenophobic() { Console.WriteLine( "Bloody foreigners ... " ); } } class CApplication { public static void RevealYourStereotype( Stereotype[] stereotypes ) { foreach( Stereotype s in stereotypes ) s(); } public static void Main() { CAmerican chuck = new CAmerican(); CBrit edward = new CBrit(); // Create our list of sterotypes. Stereotype[] stereotypes = new Stereotype[2]; stereotypes[0] = new Stereotype( chuck.BePatriotic ); stereotypes[1] = new Stereotype( edward.BeXenophobic ); // Reveal yourselves! RevealYourStereotype(stereotypes ); } } This produces the following result: ... ... God bless America. Bloody foreigners ...

5. Does C# Support a Variable Number of Arguments?

Yes, using the params keyword. The arguments are specified as a list of arguments of a specific type, e.g. int. For ultimate flexibility, the type can be object. The standard example of a method which uses this approach is System.Console.WriteLine().

6. How can I process command-line Arguments? Like this: using System; class CApp { public static void Main( string[] args ) { Console.WriteLine( "You passed the following arguments:" ); foreach( string arg in args ) Console.WriteLine( arg ); } }

7. What is Key Qualifier? The Key qualifier indicates whether the property is part of the namespace handle. If more than one property has the Key qualifier, then all such properties collectively form the key (a compound key).

8. Explain Shadowing? When two programming elements share the same name, one of them can hide — that is, shadow — the other one. In such a situation, the shadowed element is not available for reference; instead, when your code uses the shared name, the Visual Basic compiler resolves it to the shadowing element. An element can shadow another element in two different ways. The shadowing element can be declared inside a subregion of the region containing the shadowed element, in which case the shadowing is accomplished through scope. Or a deriving class can redefine a member of a base class, in which case the shadowing is done through inheritance. Public Class BaseCls Public Z As Integer = 100 ' The element to be shadowed. End Class Public Class DervCls Inherits BaseCls Public Shadows Z As String = "*" ' The shadowing element. End Class Public Class UseClasses Dim BObj As BaseCls = New DervCls() ' DervCls widens to BaseCls. Dim DObj As DervCls = New DervCls() ' Access through derived class. Public Sub ShowZ() MsgBox("Accessed through base class: " & BObj.Z) ' Outputs 100. MsgBox("Accessed through derived class: " & DObj.Z) ' Outputs "*". End Sub

End Class

9. Explain and give an example of Finally? The finally block is useful for cleaning up any resources allocated in the try block. Control is always passed to the finally block regardless of how the try block exits. In this example, there is one illegal conversion statement that causes an exception. When you run the program, you get a run-time error message, but the finally clause will still be executed and display the output. // try-finally using System; public class TestTryFinally { public static void Main() { int i = 123; string s = "Some string"; object o = s; try { // Illegal conversion; o contains a string not an int i = (int) o; } finally { Console.Write("i = {0}", i); } } }

Output The following exception occurs: System.InvalidCastException Although an exception was caught, the output statement included in the finally block will still be executed, that is: i = 123 For more information on finally, see try-catch-finally.

10. What is GAC? Define. Global assembly catch. Gacutil is to view assembly files.

VB.NET and Database 1. Overview: VPN? Using a Virtual Private Network (VPN) is the most secure option for implementing replication over the Internet. VPNs include client software so that computers connect over the Internet (or in special cases, even an intranet) to software in a dedicated computer or a server. Optionally, encryption at both ends as well as user authentication methods keep data safe. The VPN connection over the Internet logically operates as a Wide Area Network (WAN) link between the sites. A VPN connects the components of one network over another network. This is achieved by allowing the user to tunnel through the Internet or another public network (using a protocol such as Microsoft Pointto-Point Tunneling Protocol (PPTP) available with the Microsoft® Windows NT® version 4.0 or Microsoft Windows® 2000 operating system, or Layer Two Tunneling Protocol (L2TP) available with the Windows 2000 operating system). This process provides the same security and features previously available only in a private network.

2. Explain About Two-Tier, Three-Tier and N-Tier System?

3. Overview: RCW and CCW?

When a .NET client activates a COM object, the runtime generates an instance of the runtime callable wrapper (RCW) to wrap the COM type. As the following illustration shows, the runtime uses metadata obtained from an imported COM type library to generate the RCW. The wrapper marshals data according to the rules established by the interop marshaling service.

There are two ways to customize an RCW. If you can modify the Interface Definition Language (IDL) source, you can apply type library file (TLB) attributes and import the type library. Alternatively, you can apply interop-specific attributes to imported types and generate a new assembly. Support for customizing standard RCWs is limited by these attributes.

To modify the IDL source 1. Apply TLB attributes to libraries, types, members, and parameters. Use the custom keyword and an attribute value to change metadata. By applying TLB attributes, you can: • Specify the managed name of an imported COM type, instead of allowing the import utility to select the name according to standard conversion rules. • Explicitly define a destination namespace for the types in a COM library. 2. Compile the IDL source code. 3. Generate an assembly from the resulting type library file or from a dynamic link library file (DLL) that contains the type you intend to implement. To modify an imported assembly 1. Import the type library file. Use the Type Library Importer (Tlbimp.exe) to generate an assembly DLL. 2. Create a text file from the imported assembly by using the MSIL Disassembler (Ildasm.exe). 3. Apply interop attributes to the text file. 4. Generate a new assembly from the modified text file by using the MSIL Assembler (Ilasm.exe). COM Callable Wrappers A COM callable wrapper (CCW) exposes .NET Framework objects to COM. By compiling a managed project into an assembly DLL, you automatically create the metadata required to describe

each type in the assembly. The runtime uses this metadata to generate a CCW whenever a COM client activates managed object. To customize a CCW, apply interop-specific attributes to your managed source code and compile the source into an assembly, as shown in the following illustration. In this example, Tlbexp.exe converts managed types to COM.

CCW generation and method calls

By applying attributes to your code, you can alter interface and data marshaling behavior within the confines of the interop marshaling service. For example, you can control the format of the data passed as an argument to a method. You can also control which types in an assembly are exposed to COM.

4. Difference between Typed data set and Untyped data set?

The difference between the two lies in the fact that a Typed DataSet has a schema and An Untyped DataSet does not have one. It should be noted that the Typed Datasets have more support in Visual studio. A typed dataset gives us easier access to the contents of the table through strongly typed programming that uses information from the underlying data schema. A typed DataSet has a reference to an XML schema file: Dim s As String s = dsCustomersOrders1.Customers(0).CustomerID In contrast, if we are working with an untyped DataSet, the equivalent code looks like this: Dim s As String s = CType ( dsCustomersOrders1.Tables ( "Customers" ). Rows(0). Item ("CustomerID"), String) As the syntax is much simpler and more practical, using typed Datasets is much more handy. 5. Difference between Clustered Index and Non-Clustered index?

Clustered Index - Orders the records in the table based on the primary key. Only allowed one Clustered Index per table. Non-Clustered Index - creates a list of presorted pointers to the recors in a table. Allowed multiple non-clustered indexes per table. 6. What is Function, View and Stored procedure in SQL Server 2000? A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions: •

Restrict a user to specific rows in a table. For example, allow an employee to see only the rows recording his or her work in a labortracking table.

•

Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.

•

Join columns from multiple tables so that they look like a single table.

•

Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.

Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, titleview in the pubs database is a view that selects data from three base tables to present a virtual table of commonly needed data: CREATE VIEW titleview AS SELECT title, au_ord, au_lname, price, ytd_sales, pub_id FROM authors AS a JOIN titleauthor AS ta ON (a.au_id = ta.au_id) JOIN titles AS t ON (t.title_id = ta.title_id) You can then reference titleview in statements in the same way you would reference a table: SELECT * FROM titleview

A view can reference another view. For example, titleview presents information that is useful for managers, but a company typically discloses year-to-date figures only in quarterly or annual financial statements. A view can be built that selects all the titleview columns except au_ord and ytd_sales. This new view can be used by customers to get lists of available books without seeing the financial information: CREATE VIEW Cust_titleview AS SELECT title, au_lname, price, pub_id FROM titleview Views can be used to partition data across multiple databases or instances of Microsoft® SQL Server™ 2000. Partitioned views can be used to distribute database processing across a group of servers. The group of servers has the same performance benefits as a cluster of servers, and can be used to support the processing needs of the largest Web sites or corporate data centers. An original table is subdivided into several member tables, each of which has a subset of the rows from the original table. Each member table can be placed in databases on separate servers. Each server also gets a partitioned view. The partitioned view uses the Transact-SQL UNION operator to combine the results of selects against all the member tables into a single result set that behaves exactly like a copy of the full original table. For example, a table is partitioned across three servers. On the first server you define a partitioned view similar to this: CREATE VIEW PartitionedView AS SELECT * FROM MyDatabase.dbo.PartitionTable1 UNION ALL SELECT * FROM Server2.MyDatabase.dbo.PartitionTable2 UNION ALL SELECT * FROM Server3.MyDatabase.dbo.PartitionTable3 You define similar partitioned views on each of the other servers. With these three views, any TransactSQL statements on any of the three servers that reference PartitionedView will see the same behavior as from the original table. It is as if a copy of the original table exists on each server, when in fact there is only one member table and a partitioned view on each table. For more information, see Scenarios for Using Views. Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view, for example: -- Increase the prices for publisher '0736' by 10%. UPDATE titleview SET price = price * 1.10 WHERE pub_id = '0736' GO SQL Server 2000 supports more complex types of INSERT, UPDATE, and DELETE statements that reference views. INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. Also, partitioned views support INSERT, UDPATE, and DELETE statements that modify multiple member tables referenced by the view.

Indexed views are a SQL Server 2000 feature that greatly improves the performance of complex views of the type usually found in data warehouses or other decision support systems. Views are called virtual tables because the result set of a view is us not usually saved in the database The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time. Complex queries, such as those in decision support systems, can reference large numbers of rows in base tables, and aggregate large amounts of information into relatively concise aggregates such as sums or averages. SQL Server 2000 supports creating a clustered index on a view that implements such a complex query. When the CREATE INDEX statement is executed the result set of the view SELECT is stored permanently in the database. Future SQL statements that reference the view will have substantially better response times. Modifications to the base data are automatically reflected in the view.

The SQL Server 2000 CREATE VIEW statement supports a SCHEMABINDING option that prevents the tables referenced by the view being changed without adjusting the view. You must specify SCHEMABINDING for any view on which you create an index.

7. What is Trigger? Which is new in SQL Server 2000? Microsoft® SQL Server™ 2000 triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. Triggers are powerful tools that sites can use to enforce their business rules automatically when data is modified. Triggers can extend the integrity checking logic of SQL Server constraints, defaults, and rules, although constraints and defaults should be used instead whenever they provide all the needed functionality. Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column. Triggers can automate the processing for a company. In an inventory system, update triggers can detect when a stock level reaches a reorder point and generate an order to the supplier automatically. In a database recording the processes in a factory, triggers can e-mail or page operators when a process exceeds defined safety limits. The following trigger generates an e-mail whenever a new title is added in the pubs database: CREATE TRIGGER reminder ON titles FOR INSERT AS EXEC master..xp_sendmail 'MaryM', 'New title, mention in the next report to distributors.' Triggers contain Transact-SQL statements, much the same as stored procedures. Triggers, like stored procedures, return the result set generated by any SELECT statements in the trigger. Including SELECT statements in triggers, except statements that only fill parameters, is not

recommended. This is because users do not expect to see any result sets returned by an UPDATE, INSERT, or DELETE statement.

sp_helptrigger (T-SQL) Returns the type or types of triggers defined on the specified table for the current database. Syntax

sp_helptrigger [@tabname =] 'table'

[,[@triggertype =] 'type']

Arguments

[@tabname =] 'table' Is the name of the table in the current database for which to return trigger information. table is nvarchar(776), with no default. [@triggertype =] 'type' Is the type of trigger to return information about. type is char(6), with a default of NULL, and can be one of these values.

Value DELETE INSERT UPDATE

Description Returns DELETE trigger information. Returns INSERT trigger information. Returns UPDATE trigger information.

Return Code Values

0 (success) or 1 (failure) Remarks

Column name Trigger_name Trigger_owner Isupdate

Data type sysname sysname int

Isdelete

int

Isinsert

int

Permissions

Description Name of the trigger Name of the trigger owner 1 = UPDATE trigger 0 = Not an UPDATE trigger 1 = DELETE trigger 0 = Not a DELETE trigger 1 = INSERT trigger 0 = Not an INSERT trigger

Execute permissions default to the public role. Examples

This example creates a trigger named sales_warn that raises error 50010 when the amount of books sold is 10. Then, sp_helptrigger is executed to produce information about the trigger(s) on the sales table. USE pubs CREATE TRIGGER sales_warn ON sales FOR INSERT, UPDATE AS RAISERROR (50010, 16, 10) EXEC sp_helptrigger sales

Here is the result set: trigger_name

trigger_owner

isupdate

isdelete

isinsert

------------- ----------------------- ----------- ----------- --------sales_warn

dbo

1

0

1

(1 row(s) affected

You can use the FOR clause to specify when a trigger is executed: •

AFTER

The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed. AFTER triggers cannot be specified for views, they can only be specified for tables. You can specify multiple AFTER triggers for each triggering action (INSERT, UPDATE, or DELETE). If you have multiple AFTER triggers for a table, you can use sp_settriggerorder to define which AFTER trigger fires first and which fires last. All other AFTER triggers besides the first and last fire in an undefined order which you cannot control.

AFTER is the default in SQL Server 2000. You could not specify AFTER or INSTEAD OF in SQL Server version 7.0 or earlier, all triggers in those versions operated as AFTER triggers. •

INSTEAD OF

The trigger executes in place of the triggering action. INSTEAD OF triggers can be specified on both tables and views. You can define only one INSTEAD OF trigger for each triggering action (INSERT, UPDATE, and DELETE). INSTEAD OF triggers can be used to perform enhance integrity checks on the data values supplied in INSERT and UPDATE statements. INSTEAD OF triggers also let you specify actions that allow views, which would normally not support updates, to be updatable. ALTER TRIGGER Examples

This example creates a trigger that prints a user-defined message to the client when a user tries to add or change data in the roysched table. Then, the trigger is altered using ALTER TRIGGER to apply the trigger only on INSERT activities. This trigger is helpful because it reminds the user who updates or inserts rows into this table to also notify the book authors and publishers. USE pubs GO CREATE TRIGGER royalty_reminder ON roysched WITH ENCRYPTION FOR INSERT, UPDATE AS RAISERROR (50009, 16, 10)

-- Now, alter the trigger. USE pubs GO ALTER TRIGGER royalty_reminder ON roysched

FOR INSERT AS RAISERROR (50009, 16, 10)

Message 50009 is a user-defined message in sysmessages. For information about creating user-defined messages, see sp_addmessage.

DROP TRIGGER (T-SQL) Removes one or more triggers from the current database.

Syntax DROP TRIGGER {trigger} [,...n] Examples

This example drops the employee_insupd trigger. USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_insupd' AND type = 'TR') DROP TRIGGER employee_insupd GO

8. Define. char, varchar, text, nvarchar and ntext?

9. What is patindex and charindex? Patindex: Returns the string position of the first occurrence of pattern Ex: Select patindex(‘%o%’, ‘Microsoft’) Ans: 5. Note: % is must Charindex: Same as patindex but you can specify the string position. Ex: charindex ( Expression1, Expression2 [ , start_location])

10. What is Stuff in SQL server 2000? Specify length of characters and inserts another set of characters at a specified starting point. Stuff(char_exp, start, length, char_exp[ New ] ) Ex: stuff(‘abcdef’, 2, 3 ‘ijklm’) Ans: aijklmdef