SQL SERVER – How to Use Instead of Trigger – Guest Post by Vikas Munjal – Koenig Solutions

A trigger is an exceptional sort of stored procedure which functions when we try to amend the data in a table like inserting, deleting or updating data.  It is a database object, executed automatically and is bound to a table. Fundamentally, triggers are classified into two types mainly-

  • Instead of Trigger
  • After Trigger

We know how we can insert, delete or update operations aligned with excessively intricate views to support natively through ‘Instead of Trigger’. In other words, we can use this trigger as an interceptor for an action attempted on our table.

Instead of Trigger is an important element which is emphasized in almost every SQL course.  Here, we will discuss the situation where we want to make a table column which can auto generate the customized sequence.  We can see an example of the same below-

Here, we don’t have to misunderstand the id column above to be an identity column. This column is of character data type . All we want is to autogenerate this column as it is displayed in the figure above.

Usage

‘Instead of Trigger’ can help us to easily solve the situation above. In ‘Instead of Trigger ‘ we insert the data into the virtual tables prior to checking the constraints.  As far as ‘After Trigger’ constraints are concerned, they are checked in the first place.  Data is then inserted into the virtual tables ( inserted and deleted tables).

We can consider the code mentioned below for better understanding-

CREATE TABLE [dbo].[Employee1](
[id] CHAR(10) PRIMARY KEY,
[name] VARCHAR(50)
)
GO
INSERT INTO [dbo].[Employee1]  VALUES('a1','John')
GO

Now, for  an id column, we need to automatically generate a2, a3, a4….. For this, we can write a code in an insert trigger. Therefore, everytime the trigger command occurs, the trigger fires and the next number is generated.

Let us consider the command mentioned under-

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')

Now we will enter the data in the column (name). By doing so, we will be entering NULL values in the column (id). We have a primary key column in the (id) column. In a primary key, there is no permission for NULL. Therefore, the Primary Key constraint is violated.

In case, we make use of ‘After Trigger’, then constraints are checked prior to inserting the data into the implied table. The primary key constraint is violated in this case.  Therefore, we can’t put data into virtual table. As a result, we will not find the trigger firing. On the other hand, on making use of ‘Instead of Trigger’, data is inserted into the virtual table prior to the constraint check.

Therefore, our virtual (inserted) table will be as-

Instead of Trigger’s code will be now fired. It is written as-

--Instead of Trigger
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[Employee1]
instead
OF INSERT AS
BEGIN
DECLARE
@ch CHAR
DECLARE
@num INT
SELECT  
@num=SUBSTRING(MAX(id),2,1) FROM [dbo].[Employee1]
SELECT  @ch=SUBSTRING(MAX(id),1,1) FROM [dbo].[Employee1]
IF @num=9
BEGIN
SET
@num=0
SET @ch= CHAR( ( 1 + ASCII(@ch) ))
END
INSERT INTO
[dbo].[Employee1] (id,name) SELECT (@ch+CONVERT(VARCHAR(9),(@num+1))),inserted.name FROM inserted
END

Explanation of the Code Above

The trigger’s code gets the greatest value from the id column. This is done when we use MAX(id)function, parse the integer data and the character. Now with the use of substring function, put it in @ch and @num variables respectively.

When @num turns 9 then @num is reset to 0. The character is then increased to the next character.

For instance, if @ch= 'a' then
ASCII('a')=97
@ch=CHAR(1+97)=CHAR(98)='b'

Soon after, @num raises by 1 and gets coupled with the @ch variable. Then, it will be placed into the dbo.employee1 table.

Now we can run the commands mentioned under-

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alan')
INSERT INTO [dbo].[Employee1] (name) VALUES('Mike')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rahul')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vikas')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vijay')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vineet')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rajat')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alice')
SELECT * FROM [dbo].[Employee1]

With the information provided in the piece of writing above, we know how important is Instead of Trigger in SQL.   It provides a clear approach to modify views that we cannot change directly through DML statements (INSERT, UPDATE, and DELETE).

NOTE: It is always a good idea to design your system such a way that you do not need trigger to use. You can include the logic of trigger in your code as well in your procedure and avoid the usage of the trigger. Triggers are very difficult to debug as well adds lots of overhead to the system. There are many performance problems due to poor implementation of the trigger. This post is just created for demonstration of how triggers can be used in special cases.

Guest Author

The author of the article Vikas Munjal is SQL Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, SharePoint, SQL Training, Prince2, etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

I had previously written two articles about an intriguing observation of triggers online.

SQL SERVER – Interesting Observation of Logon Trigger On All Servers

SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

If you are wondering what made me write yet another article on logon trigger then let me tell you the story behind it. One of my readers encountered a situation where he dropped the database created in the above two articles and he was unable to logon to the system after that.

Let us recreate the scenario first and attempt to solve the problem.

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO
AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO
/* Dropping Database AuditDB */
/* Please note login to Server again will
Produce Errors */
USE master
GO
DROP DATABASE AuditDB
GO

After the database is dropped and we try to login again the following error will be displayed.

Logon failed for login ‘SQL\Pinal’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

This error could have been evaded if the Trigger was dropped prior to dropping database.

USE master
GO
DROP TRIGGER Tr_ServerLogon ON ALL SERVER
GO

Now, it will not be possible to logon to the database using SQL Server Management Studio. The only way to fix this problem is using DAC. Read more details about DAC  Using a Dedicated Administrator Connection to Kill Currently Running Query. Now let us take a look at the example in our case where I am using windows authentication to logon to the system.

Connect SQL Server using sqlcmd and -A option, which will establish DAC. Running the following command on command prompt will allow you to login once again in the database. I am specifying argument -d master which will directly enable me to logon to master database when DAC is connected.

C:\Users\Pinal>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER Tr_ServerLogon ON ALL SERVER
2> GO

I hope my explanation on logon triggers, DAC, and sqlcmd is clear to everyone. Let me have your thoughts about my present article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

Does the title of this post trigger your mind? If you all remember, a few days back I had written an article on my interesting observation regarding logon triggers. I would advise you to first read SQL SERVER – Interesting Observation of Logon Trigger On All Servers before continuing with this article further to have a complete idea of the subject.

The question I put forth in my previous article was – In single login why the trigger fires multiple times; it should be fired only once. I received numerous answers in thread as well as in my MVP private news group. Now, let us discuss the answer for the same.

The answer is – It happens because multiple SQL Server services are running as well as intellisense is turned on.

Let us verify the above answer.

First, run the following script to create database and logon Audit table.

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO
AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO

After that, disable all the other SQL Server Services as delineated in the image below using SQL Server Configuration Manager.

Next, try to logon to the system only one more time.

Check the audit table again to verify if there is a single entry for single login.

I would once again like to thank all of you for active participation and coming up with wonderful suggestions and answers. Let me have your opinion on this observation.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interesting Observation of Logon Trigger On All Servers

I was recently working on security auditing for one of my clients. In this project, there was a requirement that all successful logins in the servers should be recorded. The solution for this requirement is a breeze! Just create logon triggers. I created logon trigger on server to catch all successful windows authentication as well SQL authenticated solutions. When I was done with this project, I made an interesting observation of executing logon trigger multiple times. It was absolutely unexpected for me! As I was logging only once, naturally, I was expecting the entry only once. However, it was doing it multiple times on different threads – indeed an eccentric phenomenon at first sight!

Let us first pore over our example.

Create database, table and logon trigger

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO
AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO

Login using SQL Authentication and check audit table


Login using Windows Authentication and check audit table

The above example clearly demonstrates that there are multiple entries in the Audit table. Also, on close observation it is evident that there are multiple process IDs as well. Based on these two observations I can come to one conclusion. Similar actions like login to the server took place multiple times.

Question to readers:

Have you ever experienced this kind of situation? If yes, then have you received similar entries?

For those, who have not experienced this phenomenon yet, they can recreate this situation very quickly by running the above script, and then you all can post your observations and conclusions here.

I am very much interested in learning the cause that triggers multiple entries. Valid suggestions and explanations will be published on this blog with due credit.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guidelines and Coding Standards Complete List Download

SQL SERVER – Guidelines and Coding Standards complete List Download

Coding standards and guidelines are very important for any developer on the path of successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where they should not prevent best practices for coding. They are basically the guidelines that one should follow for better understanding.

The concept behind implementing coding standards and guidelines, is that the consistency and uniformity in programming so that if multiple people are working on the same code, it becomes easier to communicate, share with or understand each other’s work.

With the goal of promoting good coding standards and guidelines I have created document which can guide developers.

SQL SERVER – Guidelines and Coding Standards Part – 1

SQL SERVER – Guidelines and Coding Standards Part – 2

SQL SERVER – Guidelines and Coding Standards complete List Download

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Download SQL Server 2008 Interview Questions and Answers Complete List

UPDATE: This article series has been updated with new interview questions and answers series.

Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is very large subject and not everything is usually asked in interview. In interview what matters the most is conceptual knowledge and learning attitude.

I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the question please send me mail or write a comment.

SQL SERVER – 2008 – Interview Questions and Answers – Part 1

SQL SERVER – 2008 – Interview Questions and Answers – Part 2

SQL SERVER – 2008 – Interview Questions and Answers – Part 3

SQL SERVER – 2008 – Interview Questions and Answers – Part 4

SQL SERVER – 2008 – Interview Questions and Answers – Part 5

SQL SERVER – 2008 – Interview Questions and Answers – Part 6

SQL SERVER – 2008 – Interview Questions and Answers – Part 7

SQL SERVER – 2008 – Interview Questions and Answers – Part 8

Download SQL Server 2008 Interview Questions and Answers Complete List

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers – Part 1

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

1) General Questions of SQL SERVER

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read More Here)

What are the properties of the Relational tables?

Relational tables have six properties:

  • Values are atomic.
  • Column values are of the same kind.
  • Each row is unique.
  • The sequence of columns is insignificant.
  • The sequence of rows is insignificant.
  • Each column must have a unique name.

What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. (Read More Here)

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies is said to be in DKNF.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger. (Read More Here)

What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. (Read More Here)

© Copyright 2000-2009 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)