Database Files & Objects – Part 4

Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be investigating how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on functions, stored procedures, triggers and views in this article. Let’s use the USE statement to select the sample database.

VIEWS – MSDN QUOTE: “A view…

Various Triggers – Part 6

Triggers can be used to track Data Manipulation Language (DML) changes that occur in tables. Today, I am going to expand the AUTOS database script to add table auditing. Auditing is a very good practice when multiple people have rights to make changes to important semi-static data. This is not a good solution if the amount of data is large or number of table changes are huge. First, I am going to leverage the newly built schema called Audit Database Tracking (ADT) to seperate data tables from audit tables. A…

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…