Wednesday, May 22, 2013

MongoDB 2.4 Feature Demo and Q&A on Geo Capabilities

 

Watch Webinar on Geo Capabilities of MongoDB

Monday, May 20, 2013

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.

Sunday, May 19, 2013

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.

Wednesday, May 15, 2013

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