SQL VIEW,INDEX,TRIGGER
EXAMPLES
select * from course
use sam
create trigger MYTRIGSAMPLE ON
course
AFTER INSERT
AS
BEGIN
DECLARE @reg varchar(50)
SET @reg=(select sregno from INSERTED )
if (SELECT count(* ) from course where
sregno=@reg)>1
begin
ROLLBACK TRANSACTION
RAISERROR('Regno is already Exist',1,1,@reg)
end
end
insert into course values('107','MCA','2012','I')
select * from tbltrigger
alter table tbltrigger add
DELETED bit null
alter table tbltrigger drop
column DELETED
update tbltrigger
set DELETED =0
---------In this
trigger shows how to delete a record...and it's maintain deleted record---
--so we can back
up the deleted rocords--------
create trigger delname on
tbltrigger instead of
delete
as
set nocount on
update tt set tt.DELETED=1
from tbltrigger
tt inner join
DELETED D ON tt.sregno=D.sregno
select * from tbltrigger
delete top (1) from tbltrigger
delete from tbltrigger
select count(*) from tbltrigger
-----------------creating
views-----------
create view
VWrecords
as
SELECT studentdetails.sregno, studentmark.mark, studentdetails.sname
FROM studentdetails INNER JOIN
studentmark ON studentdetails.sregno
= studentmark.sregno
-------------
select * from VWrecords
----alter
view----------
create view VWtriggersample with
schemabinding
as
SELECT sname,
sregno
FROM dbo.tbltrigger
-------------
select * from VWrecords
----------------Using
index--------------------
create unique clustered index [samindex]
on
[VWtriggersample]
(
sregno asc
)
select * from
VWtriggersample where sname='pmp'
CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);
select * from Employee_Test
select * from
Employee_Test_Audit
CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
insert into Employee_Test values('pmp',5.5)
------Now, a
record has been inserted into the Employee_Test table. The AFTER INSERT trigger
attached to this table has inserted the record into the Employee_Test_Audit
as:-------
---when u insert
into Table1----after ---the same record inserted into table2 automatically
using Trigger--
CREATE TRIGGER trgAfterInsert ON
[dbo].[Employee_Test]
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After
Insert Trigger.';
insert into
Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER
INSERT trigger fired.'
GO
alter trigger trigsubquery on
subquery
FOR INSERT
AS
declare @sname varchar(50);
declare @m1 int;
declare @m2 int;
declare @tot int;
select @sname=i.sname from inserted i;
select @m1=i.s1 from inserted i;
select @m2=i.s2 from inserted i;
set @tot=@m1+@m2;
--select
@tot=i.tot from inserted i;
--use to move
data to another table------
--update
subquery set tot=@tot;3
insert into subquery values(@sname,@m1,@m2,@tot)
--delete from
subquery where tot=NULL and sname=@sname
PRINT 'AFTER INSERT trigger Fired'
GO
insert into subquery(sname,s1,s2) values('PRABU1',85,55)
-------UPDATE
ALL RECORD WITH SUM TWO ROW-----------
select MAX(s1),MAX(s2) from subquery
select avg(s1+s2) from subquery
update subquery set tot=0
update subquery set tot=(s1+s2) /2
select * from subquery
SELECT
MAX(dDate) maxDate
FROM subquery UNPIVOT ( dDate FOR nDate IN ( s1, s2) ) as u
GO
SELECT
CASE
WHEN s1
>=s2 AND THEN s1
WHEN s2
>= s1 AND THEN s2
ELSE
s1
END AS from subquery
select max(dateMaxes)
from (
select
(select max(s1) from subquery) date1max,
(select max(s2) from subquery) date2max
) myTable
unpivot
(dateMaxes For fieldName In (date1max, date2max)) as tblPivot
-----------logic
to sum two rows--and update to 3 column------
create proc testex1
@sname varchar(50)
AS
declare @var varchar (50)
SET @var =
(
select (s1 +s2) from subquery where
sname=@sname
)
update subquery set tot=@var where sname=@sname
create proc testex1new
AS
declare @var varchar (50)
declare @sname varchar(50)
SET @var =
(
select (s1 +s2) from subquery where
sname=@sname
)
update subquery set tot=@var where sname=@sname
exec testex1 'PRABU1'
select * from subquery
CREATE TRIGGER trgAfterUpdate ON
[dbo].[Employee_Test]
FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
if update(Emp_Name)
set @audit_action='Updated Record -- After
Update Trigger.';
if update(Emp_Sal)
set @audit_action='Updated Record -- After
Update Trigger.';
insert into
Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER
UPDATE Trigger fired.'
GO
CREATE TRIGGER trgAfterDelete ON
[dbo].[Employee_Test]
AFTER DELETE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=d.Emp_ID from deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
set @audit_action='Deleted -- After Delete
Trigger.';
insert into
Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER
DELETE TRIGGER fired.'
GO
---when u delete
a record in Table1----after ---the same
record deleted and inserted into table2 automatically using Trigger--
CREATE TRIGGER trgInsteadOfDelete ON
[dbo].[Employee_Test]
INSTEAD OF DELETE
AS
declare @emp_id int;
declare @emp_name varchar(100);
declare @emp_sal int;
select @emp_id=d.Emp_ID from deleted d;
select @emp_name=d.Emp_Name from deleted d;
select @emp_sal=d.Emp_Sal from deleted d;
BEGIN
if(@emp_sal>1200)
begin
RAISERROR('Cannot delete where salary
> 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Test where
Emp_ID=@emp_id;
COMMIT;
insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of
Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO
-----------------loop
in SP------------
DECLARE @Iterator INT
SET @Iterator = 0
WHILE (@Iterator < 4)
BEGIN
UPDATE
SomeTable
SET
SomeField =
@Iterator
WHERE
AnotherField =
SomeCritiria
Set
@Iterator = @Iterator +
1
END
--------
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO
-----------example-----------
select * from studentmark
------------------------------------------------
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO
SELECT ( SELECT MAX(RowVals) AS MaxRowVals
FROM ( VALUES ( s1), ( s2) ) AS t1 ( RowVals )
) AS RowMax
FROM
subquery AS t
No comments:
Post a Comment