Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…
Tag: DDL
Schema Design with SSMS
Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…
Database Design with SSMS
Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…
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.
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000; font-size:small;">-- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT') DROP SCHEMA [ADT]; GO -- Create a ADT schema CREATE SCHEMA [ADT] AUTHORIZATION dbo; GO </span> |
Second, I am going to create a table to hold the keep track of the DDL changes that are…
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…
Various Triggers – Part 1
I am going to wrap up my discussion of LOGON TRIGGERS. The number and types of database triggers available to database administrators has increased over the years as the Microsoft SQL Server database engine has matured. There are now triggers for execution after both DDL and DML modifications. These triggers can be classified as AFTER – TSQL code to be executed after an action or INSTEAD OF – TSQL code to be executed in lue of a action. Triggers are commonly used in following tasks: Auditing data changes by place…