MongoDB 2.4 Feature Demo and Q&A on Geo Capabilities

 

Watch Webinar on Geo Capabilities of MongoDB

Before Triggers in SQL Server

There are lots of questions asking whether there are before triggers in SQL Server. There are nothing called Before Triggers in SQL Server.

What is the requirement for the Before Trigger?

Let us say, you want to verify some values other table before inserting it. In SQL Server, you can use INSTEAD OF TRIGGER.

CREATE TRIGGER tr_data_before ON Table_Data
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM Tablle_Data2)
BEGIN
    INSERT INTO dbo.Table_Data
    SELECT *
    FROM INSERTED
END
END

However, in Oracle and DB2 there are Before Triggers.

What Operation Type Invoked a Trigger?

This was a question brought to my attention and I didn’t have an answer. After searching, I realized that there is no direct function for it. Hence I came up with following solution.

I used inserted and deleted virtual tables.

Statement	inserted	deleted
INSERT	rows just inserted
DELETE		rows just deleted
UPDATE	modified row contents	original row contents

With the above cases, I came up with following trigger.

CREATE TRIGGER trg_data_ins_del_upd
ON Data
FOR INSERT,DELETE,UPDATE
AS
BEGIN
    DECLARE @ins int ,@del int

    SELECT @ins = Count (*) From inserted
    SELECT @del = Count (*) From deleted
    IF @ins > 0 AND @del > 0 
       INSERT INTO Operation  (Operation)  VALUES  ('Update')
    ELSE IF @ins > 0 
       INSERT INTO Operation  (Operation)  VALUES  ('Insert')
    ELSE IF @del > 0 
       INSERT INTO Operation  (Operation)  VALUES  ('Delete')
END

However, I feel in the above case, you better of having three separate triggers than loading everything to one trigger.

Infrastructure as a Service for SQL Server

SQL Server Virtual images available for SQL 2008 R2 and SQL Server 2012 in 3 editions – Web, standard and enterprise.  If you have an existing SQL Server license with software assurance, as an additional benefit you can move your existing license to Windows Azure and simply pay for compute and storage.                 

Prices are determined based on

• Size of the database
  • Web Edition if size of database < 5 GB
  • Business Edition if size of database < 150 GB
  • Least unit for which prices are charged is a GB. Example: If a database is 2.4 GB,  charges will be levied for 3 GB

• Computing power required

• Version of License

your free trial contains

 

http://www.windowsazure.com/en-us/pricing/free-trial/

virtual machines & cloud services / 750 compute hours per month

• SQL server / 750 hours of web, standard, or enterprise
• web sites / 10 web sites
• mobile services / 10 mobile services
• relational database / 1 SQL database
• SQL reporting / 100 hours per month
• storage / 70 GB with 50,000,000 storage transactions
• backup / 20 GB
• data transfer / unlimited inbound & 25GB outbound
• media services encoding / 50 GB (input & output combined)
• cdn / 20 GB outbound with 500,000 transactions
• cache / 128 MB
• service bus / 1,500 relay hours and 500,000 messages