Sql Server Trigger for Beginner

                                                    Trigger

A trigger is a SQL procedure  that initiates an action  .when an event (INSERT,UPDATE,DELETE) occures

Triggers can be viewed as similar to stored procedure in that both consist of procedural logic that is stored at the database level.

STEP 1:

CREATE TABLE  Student(SID int IDENTITY, SNAME varchar(10))

create a trigger that displays the count student table when a row is inserted into the table to which it is attached.

STEP 2:

CREATE TRIGGER tr_student_insert

ON STUDENT

FOR INSERT

AS

SELECT COUNT(*) AS 'NUMBER OF RECORD' FROM  Student

STEP 3:

INSERT INTO Student VALUES('SUTHAHAR')

RESULT:

Use the inserted and deleted Tables

STEP 1:

CREATE TABLE  STUMARK(SID int IDENTITY, MARK NUMERIC(10))

STEP 2:

CREATE TRIGGER tr_STUMARK_insert

ON STUMARK

FOR INSERT

AS

IF((SELECT MARK FROM  inserted)  < 40)

BEGIN

PRINT 'FAIL'

END

ELSE

BEGIN

PRINT 'PASS'

END

STEP 3:

INSERT INTO STUMARK VALUES(78)

OUTPUT:

AFTER UPDATE

CREATE TRIGGER tr_STUDENT_UPDATE

ON STUDENT

AFTER UPDATE

AS

AFTER DELETE

CREATE TRIGGER tr_STUDENT_DELETE

ON DELETE

AFTER UPDATE

AS

INSTEAD  OF TRIGGERS

CREATE TRIGGER tr_STUDENT_INSERT_InsteadOf

ON STUDENT

INSTEAD OF INSERT

AS

PRINT 'Updateable Views are Messy'

go

CUBE,COMPUTE,COMPUTE BY and ROLLUP OPERATOR IN SQL SERVER

The CUBE,COMPUTE,COMPUTE BY and ROLLUP operators are useful in generating reports that contain subtotals and totals. 

There are extensions of the GROUP BY clause.

The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY; however, ROLLUP has these advantages:

• ROLLUP returns a single result set; COMPUTE BY returns multiple result sets that increase the complexity of application code.
  
  
• ROLLUP can be used in a server cursor; COMPUTE BY cannot.
  
  
• The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

STEP 1:

create table JSSTUD(COL nvarchar(20),PER numeric(10))

STEP 2:

INSERT INTO JSSTUD VALUES('JJ COLLEGE',100) 

INSERT INTO JSSTUD VALUES('JJ COLLEGE',200) 

INSERT INTO JSSTUD VALUES('JJ COLLEGE',300) 

INSERT INTO JSSTUD VALUES('PSG',150) 

INSERT INTO JSSTUD VALUES('PSG',50) 

INSERT INTO JSSTUD VALUES('PSG',1050) 

INSERT INTO JSSTUD VALUES('GRG',2000) 

STEP 3:

ROLLUP

SELECT  COL,SUM(PER) FROM JSSTUD GROUP BY COL

WITH ROLLUP

OR

COMPUTE  bY

SELECT COL,PER FROM JSSTUD ORDER BY COL

COMPUTE SUM(PER) BY COL

OR

SELECT COL,PER FROM JSSTUD ORDER BY COL

COMPUTE SUM(PER) 

CUBE

select COL, sum(per) 

from JSSTUD

group by COL with CUBE