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…

Various Triggers – Part 5

Another usage of Database Triggers is track Data Definition Language (DDL) changes that occur in a database. Today, I am going to enhance the AUTOS database script to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes. First, I am going to create a schema called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables.

Second, I am going to create a table to hold the keep track of the DDL changes that are…

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…