SQL Server Instead Of Trigger with Sequence Table Sample
Using SQL Server Instead of Trigger defined on a table in combination with a sequence table and T-SQL features like SQL Output clause and T-SQL Row_Number function I will show how to insert the identity column value of a table from the sequence table.
I faced this sql problem while talking with a SQL developer friend of mine. He was required to use a sequence table in a Microsoft SQL Server 2008 database. Perhaps you already know, a new feature in Denali or SQL Server 2011 is SQL Sequence table usage. But unfortunately, if you are not using SQL2011 then SQL developers should have to implement the sequence table by themselves.
Another requirement was to use the sequence table or to read from the sequence table in SQL trigger, which means in this case a SQL Instead of Trigger. The solution was reading Identity column values from sequence table within the Instead of Insert SQL Trigger, and insert new Identity values from Sequence table into the target database table.
SQL Sequence Table in SQL Server 2008
T-SQL developers can start coding sql for solution of the problem by creating sequence table in SQL Server 2008. In fact, this SQL sequence table can be used in SQL Server 2005 as well.
Below is the t-sql create script for the sequence table.
Create Table SequenceTable (
Id int identity(1,1) not null,
dummy bit null
)
Besides identity column in SQL Server sequence table, I also added a dummy column in order to insert new rows into this table easier. To keep small this column width you can use a null bit data type as seen in the sample above. After each SQL Insert command over this SQL Server Sequence table, I will read the auto identity column values using the SQL Output Clause which is new with SQL Server 2005. And this Id values will be used as the identity values for the main table.
SQL Database Table with Identity Values from Sequence Table
And now we can create SQL Server database table whose identity values will be read from SQL Sequence Table. For simplicity I did not add all used fields, but a few table columns to make the sql sample code understandable.
Create Table Software (
EntityId int not null,
Name varchar(100),
Category varchar(100)
)
As t-sql developers will realize, the above EntityId column is not an auto identity column, because in the create script the "identity(1,1)" is missing. This is because within the Instead Of trigger, we will read the EntityId column value from the SQL Server sequence table.
SQL Server Instead Of Trigger
In SQL Server Instead Of trigger can be used especially while inserting new rows into SQL views. When an Insert command is executed on a SQL view or on a database table, if defined the SQL Instead Of Insert trigger will work. The sql code within the Instead Of Insert trigger will be executed instead of the Insert command. So sql developers or database administrators can add additional controls and checks or run sql scripts prior the row insertion takes place.
In order to successfully insert data using Instead Of Insert trigger, tsql developers should explicitly execute Insert Into script within the Instead Of trigger code. I will show soon this example, and I hope the above commends will be more clear.
Here is the basic syntax of SQL Server Instead Of Insert trigger.
Create Trigger trg_SoftwareInsteadOfInsert on dbo.Software Instead Of Insert
AS
Begin
-- SQL code required to read identity from sequence table
-- SQL code to insert data into Software data
End
Now we can fill the missing transact-sql code which will read the next number from sequence table we have created in our SQL Server 2008 database. And using the identity value read from sequence, we will insert new row into Software table.
The below t-sql code is the final SQL Server Instead Of Insert trigger create script which reads identity from auto number generator sequence table, and insert new records into target database table.
Create Trigger trg_SoftwareInsteadOfInsert on dbo.Software Instead Of Insert
AS
Begin
Create Table #temp (rn int Identity(1,1), Id int)
Insert Into SequenceTable
Output Inserted.Id Into #temp(Id)
Select null From inserted i;
With CTE as (
Select *, rn = ROW_NUMBER() OVER (Order By NewID()) From inserted i
)
Insert Into Software
Select t.Id, i.Name, i.Category
From CTE i
Inner Join #temp t ON i.rn = t.rn
End
Please note that the above Instead Of trigger is using many new SQL Server 2005 features like SQL Output Clause in Insert command, CTE common table expression statements and Row_Number() sql window functions.
Let's now make a test and see how will the above t-sql code and SQL Server Instead Of trigger work?
First increase the number generator sequence table by inserting a null value. This will make us help test the sequence number table.
After the SQL Sequence table insert, the next number will be 2.
Then we can insert 3 rows to test the sql trigger with multiple inserts.
insert into sequencetable select null
insert into Software (
EntityId, Name, Category
)
select 2011, 'Red-Gate SQL Compare', 'Compare Schema Tool'
union all
select 2011, 'Red-Gate SQL Backup', 'SQL Backup Tool'
union all
select NULL, 'LiteSpeed for SQL Server', 'SQL Server Backup and Recovery Tool'
select * from Software
After the execution of above t-sql script, the SQL Server Instead Of trigger will create following table rows in Software database table.
The above result is showing that the Instead Of Insert SQL trigger is working successfully even with multiple row inserts which is not generally considered by new sql developers and SQL Server administrators. I hope this sql problem which is stated by using a sequence table in a SQL Server Instead Of Insert trigger will be useful for tsql programmers.