In this article, I would like to describe one of the most important topic in database – Trigger. My discussions here in this document will be restricted within Microsoft SQL Server Trigger. Throughout this article the following points will be conversed:

1. What is a trigger?
2. When to use triggers
3. Types of triggers with examples

1. What is a trigger?

Triggers are a special kind of database object attached to a specific table. It is a special type of “stored procedure” that executes a batch of SQL code when either an insert, update and/or delete statement is fired on a particular table. The main difference between a stored procedure and trigger is – former is a compiled collection of SQL statements which resides in database though they are not automatically run and they need to be called explicitly by the user but triggers are event driven. When the particular event on that table is called, triggers are automatically executed.

2. When to use triggers ?

Assume a scenario. You just wish to perform some job before or after an insert and/or update and/or delete statement fired on a particular table regularly. In this case, the most elegant solution should be writing a trigger on that table. The full process will occur automatically without any manual hazard. There is a special type of trigger named Instead Of trigger which is performed when user wants to perform a task Instead Of Insert/Update/Delete. 

3. Types of triggers with examples

There are three types of triggers in SQL Server:
 
a) DDL Trigger : They are mainly used for administrative work. It can fire in response to a Transact-SQL event occurred in current database or current server. But keep one thing in mind, DDL triggers can be fired only “after” DDL statements. You cannot use them as INSTEAD OF.

Syntax:

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [,...n ] ]
{ FOR | AFTER } { event_type |event_group } [,...n ]
AS { sql_statement [ ; ] [,...n] }
::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause

Example:
Using DDL trigger, you can prevent to create, update or delete any table or database or stored procedure which is very much useful for a complete database.
Create a table named TEST. Following trigger will prevent dropping a table from a database:

Step 1:
     CREATE TRIGGER PreventDropTable

     ON DATABASE

            FOR DROP_TABLE

            AS

            PRINT 'Dropping Table is not allowed.'

Step 2:
To test the Trigger, write the following command:
DROP TABLE TEST

This should throw following message:
Dropping Table is not allowed.

Step 3:
You can disable the trigger and then drop the table. Last line of the code is used to enable the trigger again.

            DISABLE TRIGGER PreventDropTable ON DATABASE;

      DROP TABLE TEST;

            ENABLE TRIGGER PreventDropTable ON DATABASE;

b) LOGON Trigger : It fires SQL statements or stored procedure in response to LOGON events. Most important thing is - Logon triggers fire after the authentication phase, but before the user session is actually established. If authentication fails, this trigger will not fire. Here all messages that are written in trigger are directly diverted to the SQL Server error log.

Syntax:

CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH [,...n] ]
{ FOR | AFTER } LOGON
AS { sql_statement [ ; ] [,...n ] [ ; ] }
::=
     [ ENCRYPTION ]
     [ EXECUTE AS Clause ]

Example:
Create LOGIN test WITH Password = 'password'
GO
Create Trigger PreventLogon
ON ALL SERVER
AFTER LOGON
AS
IF ORIGINAL_LOGIN = 'test'
ROLLBACK()

Here, trigger PreventLogon will prevent user test to log in the database on all server.

c) DML Trigger: In SQL Server there are mainly two types of DML triggers: AFTER and INSTEAD OF. It is true that there are no BEFORE trigger in SQL though it’s valid for Oracle, the following list of commands cannot be used within a trigger body:

CREATE DATABASE
ALTER DATABASE
DROP DATABASE
LOAD DATABASE
LOAD LOG
RECONFIGURE
RESTORE DATABASE
RESTORE LOG

Syntax:

CREATE TRIGGER [schema_name. ] trigger_name
ON {table | view }
[ WITH [,...n ] ]
{ AFTER | INSTEAD OF }
{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [,...n ] }
::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

AFTER trigger is the default one. You use an AFTER trigger when you want a trigger to fire only after SQL Server completes all actions successfully.
Example:

CREATE TRIGGER AfterInsert

ON TEST

AFTER INSERT

AS BEGIN

PRINT 'Inserted Successfully.'

END

This AfterInsert trigger will be fired every time after an row is inserted in TEST Table.

INSTEAD OF triggers take place instead of any SQL action being made.
It can be used as BEFORE triggers available in other RDBMS systems. Let, you define an INSTEAD OF trigger on a table FOR INSERT, the trigger will fire BEFORE the data is inserted into the table. But you can write an INSERT statement within the trigger then the insert will take place. That means, before statement trigger is fired and then event is performed. Now if you don’t write the INSERT statement within trigger then instead of insert, trigger is fired.
  
  Example:

CREATE TRIGGER InsteadOfInsert

ON TEST

INSTEAD OF INSERT

AS BEGIN

PRINT 'Value cannot be inserted. This is a master data table.'

END

This InsteadOfInsert trigger will not allow inserting any row on test table. If you wish to insert value then you have to disable the trigger first and then perform the INSERT operation.

Microsoft – Google battle in smartphones and search engines is exciting to watch

After Bill Gates’ exit, what?

A decade back, Microsoft was a towering giant in computer software industry. Today how it is performing? Has it adjusted to the new changing situation? Has it adopted itself after the exit of Bill Gates? These questions are tormenting the minds of the investors.

Will Windows 8 bring a revolution?

Microsoft has reported 5 lakh downloads of Windows 8 preview by the developers. It will vie with Apple Inc. Microsoft’s operating system can run handheld touch-screen computers, aimed at narrowing the lead of Apple’s iPad in the tablets market. Microsoft was under pressure to come out with a Windows software capable of running thinner and smaller tablets with battery life to challenge that of Apple’s iPad. The launching of this Windows 8 software will catapult Microsoft into the top slot at par with Apple and Google’s Android software. Yahoo, AOL and Microsoft, the three major technological giants have hitherto competed for digital advertising revenue. Now they have come together in partnership in which they will sell ads for one another. Google dominates search advertising market with its Google Adsense. By joining together and selling for one another, the three companies hope to reduce the need for third party ad networks that sell some of the less desirable ad space.

 

PC sale is declining

Microsoft’s Windows sales missed analysts’ estimates for the third quarter in a row. The reason for this is the consumers’ preference for notebooks and tablets instead of PCs. In 2011, companies will be shipping 60 million tablets and nearly three fourths of them will be Apple’s iPads. This figure will rise further to 275 million units in 2015. Apple’s market share is expected to come down to around 45% at that time. In the Quarter ended June 2011, Apple sold 9.3 million iPads. Nearly a fifth of this figure was to customers who chose to buy Apple’s iPads instead of a PC.

But PC is irreplaceable

But Microsoft need not unduly worry about this trend. PC will never die a death. There are many things that can be conveniently done with a PC, which cannot be done with a smartphone or a tablet. I am a freelance journalist and I have contributed more than 3000 articles to various publications and websites. Before the advent of computers and the Net, I was typing all these articles with the help of a typewriter, which only recently I donated to a typewriting institute. After this, I started preparing my articles in a PC and sending them to the websites straightaway. Now imagine, can I do all these things in a tablet or a smartphone? I can type with a speed of hundred words per minute in a PC or typewriter. But I cannot produce even a fraction of that speed in a smartphone and tablet. Therefore PCs are indispensable for certain types of works and cannot be dispensed with.

Google-Motorola pact may favour Microsoft?

Microsoft has introduced its Express Mouse. It can track virtually any surface. Its sporty finish in pink colour is appealing. The Google-Motorola deal is indirectly helping Microsoft. Google’s purpose of acquiring Motorola was for patents as it was losing the patent cases filed by Apple in various courts. But this move has created fear psychosis in the minds of players like Samsung, LG, and Hutch etc who are using Google’s Android software in their mobile phones. Now that Google itself has become a competitor for them, it is unlikely that they will continue to rely on Android for their future mobile phones. At the earliest opportunity, they will jump into another software and here is what Microsoft can gain. If Microsoft induces these players to opt for its software, then its business and revenue will grow and so also profits. Mobile phone players do not have any reservations about Microsoft because till now Microsoft is not a player in the mobile phone hardware industry.

Will Bing challenge Google?

The biggest challenge that Microsoft is facing is whether its Bing search engine can effectively challenge the supremacy of Google. Google is on top of the world with regard to search engine and the ads related to it. Microsoft is spending $5 billion every year in order to inch towards the top position in this field. Google has two thirds market share in USA in search engine market and much more in other countries. In order to break this stronghold of Google, Microsoft is trying to change the definition of search engine. So far search engine helps one to find out websites based on the nouns that users feed in their browsers. But Microsoft wants to present a particular action in the search engine based on the verbs the users feed. The name search engine itself will be changed to decision engine by Microsoft. Even then, it will take quite long for Microsoft to break Google’s stronghold.

MoU with Anna University

Since its induction in late June, Microsoft’s cloud-based MS Office 365 experienced its first major outage recently for five hours. In India, Microsoft is scouting for R&D talent in smaller cities. It is to hire 300 engineers this fiscal year. Customs and Central Excise Settlement Commission has imposed a penalty of Rs.2 crore on Microsoft India and its former Chairman Ravi Venkatesan in a case of customs duty evasion involving sale of its software in India. Microsoft has opened a new R&D facility in Bangalore, the tech hub of India. Microsoft has signed a MoU with Anna University, Chennai promising to kindle innovation and developing entrepreneurial skills among the students. The programme is named as ‘I Spark’. It will provide 5.7 lakh students designer softwares. Microsoft will also directly hire from Anna University for research work.

Initiative for reliable technology

Microsoft is taking all initiatives to make technology as reliable and trustworthy as any other infrastructure in the modern world like safe water or secure roads. Microsoft has chosen Tech Mahindra to set up testing lab. Encoder Conformance Testing Lab (ECT) is for developing of IPTV solutions on Microsoft Mediaroom based softwares. It will enable the delivery of multimedia content over an IP network.

Will Bing-Baidu partnership be productive?

Coming back to search engines, Microsoft has decided to partner China’s Baidu in English search. This will help Microsoft’s Bing to penetrate China and further dent Google’s business there. Baidu enjoys three quarters of search engine market share in China. China is world’s largest Internet market. Baidu is eyeing overseas Chinese to use English words in their search engine. Google pulled out of China last year after a fight with Chinese government over censorship. The partnership between Baidu and Microsoft will automatically activate Bing if any Chinese typed an English word into Baidu search engine. There are more than 450 million Internet users in China. In the first quarter of 2011, Baidu had a 76% Chinese market share of search engines. Google had 20% and Bing a negligible share. Already Bing and Baidu had cooperation on mobile platforms and page results. But there are doubts as to whether this partnership in search engine can boost Microsoft’s share in a big way. This is because Chinese people prefer to use Chinese language in search engines to obtain results quickly and do not prefer English. An overwhelming majority of the Chinese do not understand English. Moreover, many English words are not workable in Baidu search engine. Therefore, a few Chinese, who prefer to type English words in search engines, prefer to type it in Google and obtain the desired results. Therefore analysts doubt as to whether the Bing-Baidu partnership can affect Google’s 20% domination in Chinese market.

Acquisition of Skype

Microsoft lost a patent battle in US top court. This may cost Microsoft $300 million and has already forced changes in the MS Word software. Microsoft developed the first ever consumer level device that can see in 3D. Its name is Kinect. But hackers have developed a fascination for Kinect. Hackers are turning this accessory into a motion sensing technology. Microsoft, in one of its biggest buyouts in more than three decades, will snap up Skype for a whopping $8.5 billion. As Microsoft is a cash rich company with cash on hand worth $48 billion, it does not face any problem in readying this huge cash. Skype enjoys patronage of millions of people the world over. After the acquisition, Skype will become a new business division within Microsoft and Skype CEO Tony Bates will become the President of this division. Skype will support Microsoft devices like gaming console, Xbox, Kinect and a wide array of Windows applications.

Microsoft-Nokia partnership may change smartphones equation

Microsoft’s partnership with Nokia is a challenge for Apple’s iPhone. Right now, Apple and Google’s Android dominate the smartphones market. Microsoft has failed to penetrate the smartphones with its Windows Phone software. Nokia has seen its market share shrink in USA. Its alliance with Microsoft will result in its switching of its existing Symbian software with Windows software of Microsoft. Nokia hopes to capture 20% of the market share in US in 2015 with the Windows software.

Why force customers to run for a third party software?

Many customers have grievances also against Microsoft inspite of its many achievements. For example, a person who wants to convert his Power Point presentation into a DVD so that he can play it in the DVD player to a wider audience will have to resort to a third party software like Moyea Power point – DVD converter by paying $100 separately. Why can’t Microsoft itself provide such a facility in its Power Point software, more especially when the customer is paying for acquiring the MS office software? Why should Microsoft force the customer to shell out money to a third party for his need? I have given this as an example. There are many such applications that need a third party software. While some of these third party softwares can be acquired free of cost, some others cost a lot. When a small group of people without any huge money at their disposal could develop a software by using their programming knowledge and offer it to users free of cost, what prevents Microsoft, sitting on a huge cash pile to undertake research and produce a similar programme for their clients? In future, Microsoft should provide all such softwares in its MS Office software package itself so that its customers can find a solution to their problems within MS Office itself.

We come across several documents in our day-to-day working. In fact, documents have existed from the beginning of civilization. It has, therefore, always been important to manage them so that their intended meaning is clear. Earlier, the documents recorded with pen and paper, were formatted by human typesetters who followed pre-defined markup instructions. For example, to provide extra information or to highlight the importance of some text in a document, the typesetters used different colors, or quotation marks or color to a document, to add special meaning to it, is referred to as markup. Definition of Markup Language. To ensure that the user understands the markups, we need to follow a set of rules. A markup language defines this set of rules, and helps add meaning to the content and structure of documents. Markup can be classified as follows: • Stylistic Markup Language • Structural Markup Language • Semantic Markup Language 1] Stylistic Markup The presentation of a document is determined by this markup. In the case of word processor applying stylistic markup would involve making the text bold, italicized or changing the font. In case of HTML and are two of the many tags that help in stylistic markup. 2] Structural Markup The structure of a document is determined by this kind of markup. This markup, for example, determines the heading or paragraph in a document. For example, the tag in HTML is two of the many tags that help to structure the document. 3] Semantic Markup The content of the document is determined by this kind of markup. Tags in HTML can be considered as examples of semantic markup. In the late 1960's, three researchers at IBM began working on the problem of dealing with documents created on disparate systems that used proprietary formats. The research made it obvious that the following three primary requirements were essential, in order to have an interoperable system: * There should be a common document format supported by the document processing programs. * The common format should be specific to their domain, for example, a domain for legal documents and a domain for medical documents. *There should be specific rules for the format of the document. The system of formatting documents was named Generalized Markup Language (GML). Over the years, GML was fine-tuned, and came to be known as Standard Generalized Markup Language (SGML). SGML is, therefore, considered to be the mother of all markup languages. SGML is a powerful language, but many of its features are rarely used because of its complexity. SGML describes markup languages, allowing authors to create their own tags that relate to the content. An SGML document needs a file, which has all the rules for the language, for its interpretation. That files needs to be sent along with the SGML document, so that the tags can be interpreted. Markup languages derived from SGML are called SGML applications. HTML is the most famous markup language derived from SGML. It has been extensively used on the web and is popular because of its simplicity. HTML was created to mark up technical papers so that they could be transferred across different platforms to be accessed by the scientific community. As the use of the Internet became popular, the non-scientific users became concerned about the presentation of their documents. Manufacturers of browsers started offering different tags that would allow authors to display documents with more creativity. The rapid growth in the numbers of tags created new problems. The implementation of the same tag in different browsers was different. Therefore, even today, we can find web sites, which inform the user as to which browser the site can best be viewed in.