Trigger and Admin Assignments

--triggers
use MetroAlt
Go
Create trigger tr_CheckDelete 
on MaintenanceDetail 
instead of delete
As
if not exists
   (Select name from sys.Tables 
   where name ='MaintenanceDetailDeletes')
Begin
    Create table MaintenanceDetailDeletes
 (
  MaintenanceDetailkey int, 
  MaintenanceKey int, 
  BusServiceKey int, 
  EmployeeKey int, 
  MaintenanceDetailNote nvarchar(255)
 )
End
Insert into MaintenanceDetailDeletes
(MaintenanceDetailkey, 
MaintenanceKey, 
BusServiceKey, 
EmployeeKey, 
MaintenanceDetailNote)
Select MaintenanceDetailkey, 
MaintenanceKey, 
BusServiceKey, 
EmployeeKey, 
MaintenanceDetailNote
from Deleted

Select * from MaintenanceDetail

Insert into BusService(BusServiceName)
values('oil change'),
('brakes')

Insert into Maintenance(MaintenceDate, BusKey)
values(getDate(),45)

Insert into MaintenanceDetail(MaintenanceKey, BusServiceKey, EmployeeKey, MaintenanceDetailNote)
values(IDENT_CURRENT('Maintenance'),1,8, 'dry as a bone')

Delete from MaintenanceDetail where MaintenanceDetailkey=1

Select * from MaintenanceDetail
Select * from MaintenanceDetailDeletes

Select * from BusScheduleAssignment

go
Create trigger tr_overtime on [dbo].[BusScheduleAssignment]
for insert
As
Declare @EmployeeKey int
Declare @Date Date
Declare @Count int

Select @employeeKey=EmployeeKey, @Date = BusScheduleAssignmentDate
from Inserted
Select @count=Count(EmployeeKey) from BusScheduleAssignment
where [BusScheduleAssignmentDate]=@Date and EmployeeKey = @EmployeeKey
if @count > 1
Begin
   if not exists
     (Select Name from sys.tables 
  where Name = 'Overtime')
  Begin
     Create table Overtime
  (
    BusScheduleAssignmentKey int, 
    BusDriverShiftKey int, 
    EmployeeKey int, 
    BusRouteKey int, 
    BusScheduleAssignmentDate Date, 
    BusKey int

  )
  End
  Insert into Overtime (BusScheduleAssignmentKey, 
  BusDriverShiftKey, EmployeeKey, BusRouteKey, 
  BusScheduleAssignmentDate, BusKey)
  Select BusScheduleAssignmentKey, BusDriverShiftKey, EmployeeKey, 
  BusRouteKey, BusScheduleAssignmentDate, BusKey
  From Inserted
End

Insert into BusScheduleAssignment( 
BusDriverShiftKey, EmployeeKey, 
BusRouteKey, BusScheduleAssignmentDate, BusKey)
Values(1,4,23,GetDate(),4)

Insert into BusScheduleAssignment( 
BusDriverShiftKey, EmployeeKey, 
BusRouteKey, BusScheduleAssignmentDate, BusKey)
Values(2,4,23,GetDate(),4)

Select * from Overtime

--Admin 
--schema
go
Create schema ManagementSchema
go
Create view managementSchema.EmployeeView
As
Select EmployeeKey, EmployeeLastName, 
EmployeeFirstName, EmployeeAddress,
EmployeeCity, 
EmployeeZipCode, EmployeePhone, 
EmployeeEmail, EmployeeHireDate
From Employee
go
Create view ManagementSchema.BusSchedule
As
Select * from BusScheduleAssignment
Go
--roles collections of permissions
Create role ManagementRole
go
Grant Select, Insert, Update on schema::managementSchema to managementRole

Create login sconger with password='p@ssword', 
default_database = metroAlt

Create user sconger for login sconger

exec sp_addrolemember managementrole, sconger

Backup database metroAlt to disk='C:\backups\MetroAlt.bak'

Restore Database metroAlt from disk='C:\backups\metroAlt'

--xml
Select * from Employee 
for xml raw('Employee'), elements,  root('Employees')