SQL Transactions – Example

So, as I wrote yesterday, I needed to create a Transaction for my SQL database.  If you’ll remember, my database was a hypothetical library in Africa.  A new book needs cataloged, and since the book table is interlinked with several other tables, you don’t want one of the inserts to succeed and another to fail, because then your tables won’t match, and that’s all bad.  So you group all the inserts into one batch called a transaction and thus ensure data integrity.  So here we insert a book into the book table, an author into the author table, and a category into the category table all at once.  Creative Commons license blah blah blah.

DECLARE @BookID int

DECLARE @AuthorID int

DECLARE @Category_ID int

BEGIN TRY

BEGIN TRAN;

INSERT Authors VALUES (‘Coppen’, ‘Ben’);

SET @AuthorID = @@IDENTITY;

INSERT Categories VALUES (‘Computers’);

SET @Category_ID = 3;

INSERT Books

Values(0763732303, ‘Artificial Intelligence Illuminated’, @AuthorID, 2004, 1,@Category_ID, NULL);

COMMIT TRAN;

END TRY

BEGIN CATCH

ROLLBACK TRAN;

END CATCH;

Advertisements

Playing catch-up, and Global Health, Part 0

I missed two days, but I am not going to stop my NaBloPoMo because of that.  What have I been doing?

Well, Sunday was church.  I have had a certain issue on my mind that I’ve been praying about, and while I don’t have a clear answer yet, things have been moving, so we’ll see.

I finished my International Affairs textbook, and wrote another essay for the class.  Two left before the end of the year – and one I’m not sure what I’m going to write about.  In SQL, I read about transactions, and I started coding one.  I think I know how to finish it, and when I do, I’ll post it here.  One more optional program after this!  I should mention, that when I’m done with these two classes, I will graduate with a Certificate in Software Development.

We finally got Minecraft installed for my son – the web page Minecraft.net does not allow you to register.  We ended up being able to register at Mojang’s homepage.

So, exploring the field of Epidemiology, as I mentioned previously, is one thing I’ve been doing.  I am taking an online class on it.  I am also working through another go-at-your-own-pace class from Harvard / EDX on Global Health.  That one has been very interesting as it has tied into a lot of things I’ve studied in International Security and International Affairs.  It even has brought back some of the reading I did on feminism; namely, philosophers like Foucault.  I would like to write more about this, so hopefully tomorrow!

Thursday the…Thirteenth!

13 for 13 on my NaBloPoMo!  Sweet!

I’m struggling with what to write about today.  I wrote an essay today on identifying the aggressor in international conflict and why it does or does not matter.  I started with Thucydides, drew on Aquinas (Just War theory) and then referenced several UN treaties and articles.  I wrote about whether or not the advent of so-called fourth generation warfare would have any effect on these criteria, since for the most part they refer to nations as the primary actors.  My paper hasn’t been graded yet; I’ll put it up once it is.  I was, however, disappointed to find that Wikipedia is still not regarded as a valid source, despite the fact that it has been shown to have no more errors than the Encyclopedia Britannica.

In other news, we gave my son Minecraft for his birthday.  However, for the last few nights we haven’t been able to download it because we keep getting a message that they are updating their servers.  As you can imagine, this is quite frustrating.

Other than that, nothing new and exciting!

Data Validation with a SQL Trigger

For our semester long project for my SQL class, we had to create an actual database and we have been entering data and creating everything from views to scripts to triggers.

My database is a hypothetical small library in Ghana, which has been funded by a donor organization with a large number of books and several computers.  My own modest collection of 800+ books serves as the actual library (I haven’t entered them all in…yet).  The trigger I wrote is for when there is a fine to be paid.  The trigger will validate the data entered in.  Here’s the code.  Creative Commons License, and all that.

CREATE TRIGGER FineTableUpdate
ON Fines
INSTEAD OF INSERT, UPDATE
AS
DECLARE @PatronNo int, @BookNo int, @BookTitle varchar(50), @BorrowedDate Date,
@FineAmount money, @PaidStatus char(2), @TestRowCount int;
SELECT @TestRowCount = COUNT(*) FROM inserted;
IF @TestRowCount = 1
BEGIN
SELECT @PatronNo = Patron_ID, @BookNo = Book_ID, @PaidStatus = PaidStatus
FROM inserted;
BEGIN
IF NOT EXISTS (SELECT * FROM Patrons WHERE Patron_ID = @PatronNo)
THROW 50001, ‘That Patron does not exist.’, 1;
IF @PatronNo IS NULL
THROW 50001, ‘Invalid Patron Number.’, 1;
IF NOT EXISTS (SELECT * FROM Books WHERE Book_ID = @BookNo)
THROW 50001, ‘That Book does not exist.’, 1;
IF @BookNo IS NULL
THROW 50001, ‘Invalid Book Number.’, 1;
SET @BookTitle = (SELECT BookTitle FROM Books WHERE @BookNo = Books.Book_ID)
SET @BorrowedDate = (SELECT Date_Borrowed FROM Lending
WHERE @PatronNo = Patron_ID AND
@BookNo = Book_ID);
SET @FineAmount = DATEDIFF(day, @BorrowedDate, GETDATE())
IF @PaidStatus <> ‘Y’ OR @PaidStatus <> ‘N’
THROW 50001, ‘Paid Status must be Y or N’, 1;

INSERT Fines
(Patron_ID, Book_ID, Book_Title, FineAmount, PaidStatus)
VALUES (@PatronNo, @BookNo, @BookTitle, @FineAmount, @PaidStatus);
END;
END;
ELSE
THROW 50027, ‘Limit INSERT to a single row.’, 1;