OK, What do you know about triggers? Did you know you can use them to run a bit of T-SQL on data changes? Those would be called Data Manipulation Language (DML) Triggers. You can set those up for tables so that a certain action occurs with each INSERT, UPDATE, or DELETE. You can build as…
Tag: SQL102
DELETE Triggers
This is the last of my three posts on triggers. We’ve already covered INSERT and UPDATE TRIGGERS. This time we’re doing DELETE triggers. All the same rules apply to DELETE triggers that apply to INSERT and UPDATE triggers. CREATE TRIGGER t_tablename_delete ON TableName AFTER|FOR|INSTEAD OF DELETE AS T-SQL code Just like for the INSERT TRIGGER,…
UPDATE Triggers
Last time we discussed triggers, we covered insert triggers. This time we’re doing UPDATE triggers. All the same rules apply to UPDATE triggers that apply to INSERT triggers. CREATE TRIGGER t_tablename_update ON TableName AFTER|FOR|INSTEAD OF UPDATE AS T-SQL code Just like for the INSERT TRIGGER, we can either run the T-SQL code after the update…
Insert Triggers
An insert trigger is a Data Manipulation Language (DML) trigger that acts when a new record is added to the table. CREATE TRIGGER t_tableName_insert ON tableName AFTER|FOR|INSTEAD OF INSERT AS Some bit of T-SQ OK, let’s walk through this. CREATE TRIGGER begins the command. The next part t_tableName_insert is the trigger’s name. I follow a…
SQL 102 – REVOKE
Like I mentioned previously REVOKE is like an undo function for GRANT and DENY. If you have a developer and you work with him for a while, you may find that he’s ready to be given a little more leeway in the database. Once you’ve decided to make a change to the permissions, you may…
SQL 102 – DENY
Alright, at this point you should know how to GRANT permission to a database object, but do you know how to DENY permission to an object? DENY uses a syntax similar to GRANT. DENY { ALL [ PRIVILEGES ] } | permission [ (column(s) ) ] [ ON securable ] TO principal I want to…
SQL 201 – Contstraints
Constraints… helpful in making sure you get the data you want out of your database! Constraints allow you to define rules that the data must follow in order to be inserted into a record. You might want to define a primary key or foreign key in order to define the relationship of two related records…
Triggers
Triggers are one of the most misunderstood features in Microsoft SQL server. Most of that misunderstanding comes from people implementing triggers with little understanding to their impact. Even worse, they’ll implement them without testing. And the worst of all, not using them, when the functionality being requested is the very definition of what a trigger…
Default Values
When you start creating tables and setting them so they can’t have NULL values, you’ll eventually need a way to define a default value for the column. A default value is one that a column would apply to its record if a value is not provided. To specify the default value in a SQL statement,…
Identity Columns
An IDENTITY column is a column that automatically gets it’s value set by the database engine when a new record is added. This is one of the oldest ways Microsoft SQL has of making sure a record is unique. Even if a user were to insert the same record twice, the IDENTITY will always be…