SQL Training - 08

DENSE_RANK, With

DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking

With :Specifies a temporary named result set

With R as

(select salary, DENSE_RANK() over (order by salary desc) as SalaryRank

from Employees

)

select salary from R where R.SalaryRank=1

with Ties

Used when you want to return two or more rows that tie for last place in the limited results set

Like Tawjihi Results,,
select top 10 with Ties *

from employees

order by salary

 Trigger 
Level 1: on Database Level
new query window and type the following  

Create trigger [tr_createTable]

on Database

for Create_Table, Alter_Table, Drop_Table, Table_Rename

as

begin

 rollback

 Print 'you are prohibited from  create, alter, drop, rename a table'

end

GO

ENABLE TRIGGER [tr_createTable] ON DATABASE

DROP TRIGGER [tr_createTable] ON DATABASE

Level 2: on Table Level

You can use one of the following to insert in Audit via trigger 

1-

Create trigger [dbo].[tr_employeeInsert]

on [dbo].[Employees]

for Insert

as

begin

 Declare @Id int

 Declare @Name varchar(50)

 Declare @Salary float

 select @Id= id, @Name=_name, @Salary=salary

 from inserted

 Insert into Employees_Audit values(@id,@name, @Salary, GETDATE());

end

2-

Create trigger [dbo].[tr_employeeInsert]

on [dbo].[Employees]

for Insert

as

begin

 Insert into Employees_Audit

 select id, _name, salary, GETDATE()

 from Inserted;

end

You can use one of the following to insert in Audit via trigger  and prevent insert using condition 

ALTER trigger [dbo].[tr_employeeInsert]

on [dbo].[Employees]

for Insert

as

begin

 Declare @Id int

 Declare @Name varchar(50)

 Declare @Salary float

 select @Id= id, @Name=_name, @Salary=salary

 from inserted

 if(@Salary=0)

 begin

  RaisError('Invalid salary', 16, 1)

  rollback

  return

 end

 Insert into Employees_Audit values(@id,@name, @Salary, GETDATE(),'Insert');

end

L To insert from view

use instead of insert to override SQL default insert statement

create view [dbo].[vu_emp] as

SELECT        Employees.id as empid, Employees._name, Employees.salary, depts.dept

FROM            Employees inner JOIN

                         depts on Employees.deptid= depts.deptid

create TRIGGER [dbo].[tr_inseadofinsert]

   ON  [dbo].[vu_emp]

   instead of insert

AS

BEGIN

       declare @deptid  int

       select @deptid=depts.deptid

       from depts inner join inserted on depts.dept=inserted.dept

       Insert into Employees

       select _name, salary, @deptid

       from inserted

END

Level 3: on Server Level
instead

create trigger tr_serverlevel

on all server

for create_table

as

begin

select  EventData()

end

after clicking on the result link it will open in xml format contains the following

<EVENT_INSTANCE>

  <EventType>CREATE_TABLE</EventType>

  <PostTime>2018-03-29T08:51:39.217</PostTime>

  <SPID>59</SPID>

  <ServerName>CI....</ServerName>

  <LoginName>MicrosoftAccount\moh...</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>Person</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>test_server_trigger</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>create table test_server_trigger( id int)</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

No lock and tran

select * from Employees with (nolock)

begin tran

       update Employees set salary=2000

       where id=2

       update Person

       set FName='A-A'

       where Id=136

commit tran