Instead of Update

Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make our VIEW correctly work with a UPDATE statement. I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that update action can be perform on two tables at the same time. The enclosed script has code to perform the following actions in the AUTOS database. 5A – Remove aggregate view 5B – Reload tables with good data 5C – Create a instead…

Instead of Delete

Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make our VIEW correctly work with a DELETE statement. I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that delete action can be perform on two tables at the same time. The enclosed script has code to perform the following actions in the AUTOS database. 5A – Remove aggregate view 5B – Reload tables with good data 5C – Create a instead…

Instead of Insert

Most information technology professionals do not know that data can be modified via a VIEW. The INSERT, UPDATE, and DELETE statements of the Data Manipulation Language (DML) can be executed on a view as long as the following set of rules are adhered to. Modifications must reference columns from only one base table. The columns must directly reference the underlying data table. I am going to continue my exploration of updatable views. Last time, we were using the [AUTOS] sample database and discovered that DELETE statements were not working the…

Database Files & Objects – Part 1

I am currently reading Kalen Delany and others book on SQL Server 2008 System internals. If you have not read this book from cover to cover, you should. It has a wealth of knowledge about database engine details. To demonstrate key ideas from the book, I am going to expand on the [MATH] database that contains PRIME numbers. The trial division algorithm inside this database uses a brute force method for calculating prime numbers. This article will focus on files and objects that can be viewed through the system views,…

Various Triggers – Part 4

Another usage of Data Modification Language (DML) triggers is the replicating data for historical tracking or data aggregation. Today, I am going to expand on the AUTOS database that I created in prior articles to demonstrate such usage. The business unit has asked us to track each macro task in the making of an automobile.   To accomplish this goal, I am going to add the following objects to the AUTOS database. STATUS_CODE – a coded version of the status of a given task. MAKE_STATUS – for a given vehicle…

Various Triggers – Part 3

A typical use of Data Modification Language (DML) triggers is to prevent unwanted data modification. AFTER triggers are great at detecting the INSERT, UPDATE or DELETE action and rolling back the changes. A good security model can prevent these types of changes and should be the first line of defense. However, there are cases in which keys to the castle have to be given out for political reasons inside the organization. In that case, this type of preventive programming can be handy. Today, I am going to revisit the AUTOS…

Crafting Databases – Part 4

The ALTER TABLE statement is part of the Data Definition Language (DDL). The statement is used to modify a table after it has been created. Today, I would like to talk about the three integrity concerns that data modelers deal with. Integrity Concerns Entity Data Integrity – Adding primary keys to makes sure rows are unique. Referential Data Integrity – Adding foreign keys to make sure relationships are maintained. Domain Data Integrity – Creating constraints or triggers enforce the range of data values. Again, we will be working with the…