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
