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.
Word-Processing:
- Efficient mode of Text editing.
- Facility of Cut, Copy, Paste.
- Search and Replace Text.
- Justification, Indentation etc.
- Pagination is the process of separating a document's text into pages. The separations between pages are called page breaks.
Spell Checking:
Microsoft Word provides several ways to check spelling and grammar :
• Automatically correct spelling and grammar.
• Automatically check spelling and grammar as you type.
• Check spelling and grammar all at once.
• Check the spelling and grammar of text in another language.
Importing/Exporting Text:
You can import data from a Word text file, such as a mail merge data source. You can insert Microsoft Access data into a new or existing Word document.
Mail Merging is the process of transferring selected information from one document to another document. You can use the Mail Merge Helper to create form letters, mailing labels, envelopes, or catalogs. The Mail Merge Helper guides you through organizing the address data, merging it into a generic document, and printing the resulting personalized documents.
Introduction of MS-Office:
Business software integration has come into own with the release of Microsoft Office, which combines a new common look and a more polished interface - with a seamless exchange of information between the office applications. Thus, we can focus on our documents, rather than on the intricacies of the software that creates them.
Word:
In addition to using word for anything you'd use a word processor for, you can use it as the foundation for creating letters, memos, reports, and any other documents that will include or be included in documents from the other office applications.
Excel:
Use Excel to formulate and analyze numbers, manage and sort lists and create graphs, maps and pivot tables. The list and numbers can be created and stored in Excel or in an external source such as an Access Database.
PowerPoint:
Use Power Point to present your thoughts, ideas and plan to various audiences - for example, your board of directors, sales prospects, Multimedia, animation and links to your office data make Power Point ideal for timely information on powerful presentation.
Outlook:
Outlook is included with office to manage pertinent information about your contacts, facilitate the scheduling of your time; navigate your hard drive, and provide a universal inbox for all of your mail source.
Access:
Use Access to store track and reports information. You can also use Access to create databases that track membership list, client data, student registrations, inventory, tracking and customer invoicing.
Opening and saving your Office documents:
By using the improved File Open and File Save dialog boxes, you can gain access to more files at one time in every Office program. Use the Places Bar to go to the folders and locations you use the most. Click History to see the last 20 to 50 documents and folders that you have worked with. Then, click the Back button to easily return to previously opened files and folders.
More Articles …
Subcategories
Web Hosting
Web Hosting is a service offered by web hosting providers to the individuals and organizations to make their websites accessible on the internet. Depending on the requirement, one can avail different types of web hosting such as shared hosting, dedicated hosting, virtual private hosting, cloud hosting etc.
Page 56 of 193