Skip to content

Instantly share code, notes, and snippets.

@DanielLoth
Last active July 24, 2021 10:54
Show Gist options
  • Save DanielLoth/76d241515655e76cadddef6ed2d373aa to your computer and use it in GitHub Desktop.
Save DanielLoth/76d241515655e76cadddef6ed2d373aa to your computer and use it in GitHub Desktop.
create procedure dbo.Attendance_Add_tr
@OrganisationId int,
@PersonId int,
@AttendanceDate date
as
set nocount on;
------------------------------------------------------------
-- Validation block
------------------------------------------------------------
set transaction isolation level read committed;
if not exists (
select 1
from dbo.Person
where OrganisationId = @OrganisationId
and PersonId = @PersonId
)
begin
return 8; -- Person does not exist
end
if exists (
select 1
from dbo.Attendance
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
)
begin
return 7; -- Attendance on the given date has already been recorded.
end
------------------------------------------------------------
-- Execute block
------------------------------------------------------------
set transaction isolation level serializable;
begin transaction;
insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
select @OrganisationId, @PersonId, @AttendanceDate
where not exists (
select 1
from dbo.Attendance
with (updlock)
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
);
if @@ROWCOUNT <> 0
begin
commit;
end
else
begin
rollback;
end;
return 0;
create procedure dbo.Attendance_Get_tr
@OrganisationId int,
@PersonId int,
@AttendanceDate date
as
set nocount on;
set transaction isolation level read committed;
select OrganisationId, PersonId, AttendanceDate
from dbo.Attendance
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate;
return 0;
create table dbo.Organisation (
OrganisationId int not null,
OrganisationName nvarchar(100) not null,
CreatedDtm datetimeoffset (0) not null,
UpdatedDtm datetimeoffset (0) not null,
constraint UC_Organisation_PK
primary key clustered (OrganisationId)
);
create table dbo.Person (
OrganisationId int not null,
PersonId int not null,
GivenNames nvarchar(50) not null,
FamilyName nvarchar(50) not null,
CreatedDtm datetimeoffset(0) not null,
UpdatedDtm datetimeoffset(0) not null,
constraint UC_Person_PK
primary key clustered (OrganisationId, PersonId),
constraint Organisation_Hosts_Person_FK
foreign key (OrganisationId)
references dbo.Organisation (OrganisationId)
);
create table dbo.Attendance (
OrganisationId int not null,
PersonId int not null,
AttendanceDate date not null,
constraint UC_Attendance_PK
primary key clustered (OrganisationId, PersonId, AttendanceDate),
constraint Person_Indicates_Attendance_FK
foreign key (OrganisationId, PersonId)
references dbo.Person (OrganisationId, PersonId)
);
@Derek-Asirvadem
Copy link

Dan

The invitation has expired. Could you please invite me again.

Cheers
Derek

@DanielLoth
Copy link
Author

Done.

@Derek-Asirvadem
Copy link

Derek-Asirvadem commented Jul 24, 2021

Dan

I am in.

Where did the comments [removed from here] go ? I have the next submission ready, but it is meaningless without the thread of previous comments.

Just looking around (I am new to GitHub).

My expectation is this:

  1. this thread is public, to allow Nicola, for the purpose of discussing the OLTP/Transaction/ACID/SQL/Template (only)
  2. the ShootingClubDatabase is a repository, where we (just you and me) will two things
    • maintain a code repository & folder for DMs - I can see all of this
    • develop a data model - where is this ?

That latter is where I expect the most back-and-forth.

If I want to comment on (eg) a particular code item, where do I do that ?

Cheers
Derek

@DanielLoth
Copy link
Author

Hi Derek,

All of the content moved here: DanielLoth/ShootingClubDatabase#2 (the pull request is titled 'V1 diagram')

At present the repository is actually public, so others can see it.
I'm happy to make it private if you'd prefer the repository itself to be private.

A second pull request with a refreshed data model is here: DanielLoth/ShootingClubDatabase#4


For commenting on code itself, such as SQL code, this pull request can be used: DanielLoth/ShootingClubDatabase#3
You can click the 'Files changed' tab, which will show all files, and then click a line of code (or drag-select multiple lines) and leave a comment.

Cheers,
Dan

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment