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

Microsoft's SQL Server 2012 to launch on March 7

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Sql Server Error :

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager. The specified module could not be found. [0x8007007e]

Solution :

 

Start ==> Run => Type below command  

 mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Thanks

Reset Identity Value Using Sql Query

Reset Identity Value Using Sql  Query 

DBCC CHECKIDENT('UserTable' , RESEED, 0)

Database Go Online and OFFline using Query

Database GO Online:

ALTER DATABASE DBTest2 SET ONLINE

Database Go Offline

 

ALTER DATABASE DBTest2 SET OFFLINE

Add / REMOVE Primary Key to existing table

CREATE TABLE WITHOUT PRIMARY KEY

CREATE TABLE JSSTUDTABLE (SNO NUMERIC(10) NOT NULL)

ADD PRIMARY KEY EXISTING TABLE

ALTER TABLE JSSTUDTABLE ADD  PRIMARY KEY (SNO);

REMOVE PRIMARY KEY EXISTING TABLE 

ALTER TABLE JSSTUDTABLE DROP  constraint  PK__JSSTUDTA__CA1EE06C63D8CE75