June 17, 2010 at 6:44 am
I have a database of about 35 tables on SQL Server 2005 Express.
Two columns are common to each table; column datecreated is the date/time a record is created while column datemodified is to save the date/time a record is updated.
I had planned to populate the columns via VB code in my project, but am open to better ideas.
If I make the columns non-nullable, then I cannot omit them from my INSERT/UPDATE statements.
If I make them nullable, then any default values (GETDATE()) that I set will not be saved.
If I make an Insert trigger for datecreated and an update trigger for datemodified, should they be BeforeInsert and BeforeUpdate or AfterInsert and AfterUpdate?
Lastly, whatever triggers I decide to write, what dynamic TSQL code can I write to automatically create identical triggers across all tables in the database? (I remember from my visits here a few years ago, many wizards here can do magic with dynamic TSQL.)
Thanks in advance.
June 17, 2010 at 7:11 am
Try something like this. It will obviously have to be adjusted for your exact requirements.
select 'CREATE TRIGGER trg' + st.name + '_AfterInsert' + '-- the rest of your trigger code' +
'UPDATE t SET datecreated = GetDate(), datemodified = GetDate() FROM ' +
QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) +
' t JOIN inserted i ON i.<PKCOLUMNS> = t.<PKCOLUMNS>'
FROM sys.columns sc
JOIN sys.tables st
ON sc.object_id = st.object_id
JOIN sys.schemas ss
ON ss.schema_id = st.schema_id
WHERE sc.name in ('datecreated','datemodified')
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 17, 2010 at 7:26 am
goodguy (6/17/2010)
If I make them nullable, then any default values (GETDATE()) that I set will not be saved.
Hello,
I don't understand what you mean by that. If a column has DEFAULT, it is used whenever you are not inserting any value into the column. If you specify some value, then this value will be saved and not the default.
Regarding datecreated, the easiest way is to create this column with DEFAULT GETDATE(). Then whenever a new row is inserted, it will have the actual date and time in this column... unless you decide that this is special case (e.g. when you are correcting some errors and want to make it appear that the row was inserted yesterday), and then you just include this column into the insert statement and specify a value.
June 17, 2010 at 8:10 am
here is something very similar to what Wayne posted, the only difference is my script makes an assumption that the FIRST column in a table is the Primary Key because it is an identity. that way the script is ready to go with no modifications.
so if the table has column_id = 1 as an identity and also has columns named Date_Created and also Date_Modified, a trigger script is generated for that table.
--this code knows that the first column is an identity, but kind of assumes it is also the PK
DECLARE @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SELECT
'CREATE TRIGGER TR_' + objz.name + '_CreatedModified' + @vbCrLf
+ 'FOR INSERT,UPDATE '+ @vbCrLf
+ ' AS ' + @vbCrLf
+ 'UPDATE myTable ' + @vbCrLf
+ 'SET Date_Created = GetDate(), ' + @vbCrLf
+ ' Date_Modified = GetDate() ' + @vbCrLf
+ 'FROM ' + QUOTENAME(sch.name) + '.' + QUOTENAME(objz.name) + ' myTable ' + @vbCrLf
+ ' INNER JOIN INSERTED i ON i.' + QUOTENAME(myPKCols.IdentColumn) + ' = myTable.' + QUOTENAME(myPKCols.IdentColumn) + ' ' + @vbCrLf
FROM sys.tables objz
INNER JOIN sys.schemas sch
ON sch.schema_id = objz.schema_id
INNER JOIN (SELECT
colz.object_id,
colz.name as IdentColumn
FROM sys.columns colz
WHERE colz.column_id = 1
AND colz.is_identity = 1) myPKCols
ON myPKCols.object_id = objz.object_id
WHERE objz.type = 'U' --only user tables
AND objz.object_id in (SELECT object_id FROM sys.columns where name = 'Date_Created')
AND objz.object_id in (SELECT object_id FROM sys.columns where name = 'Date_Modified')
/*
--make sure all my tables which meet the criteria of 1st column is an identity has these two columns.
DECLARE
@sql varchar(2000),
@SchemaName varchar(128),
@TableName varchar(128),
@IdentColumn varchar(128),
@TableID int
DECLARE c1 CURSOR FOR
--begin cursor query
SELECT
schema_name(tabz.schema_id) AS SchemaName,
tabz.name AS TableName,
colz.name AS IdentColumn,
tabz.object_id AS TableID
FROM sys.columns colz
INNER JOIN sys.tables tabz
ON colz.object_id = tabz.object_id
WHERE colz.column_id = 1
AND colz.is_identity = 1
--end cursor query
open c1
fetch next from c1 into @SchemaName,@TableName,@IdentColumn
While @@fetch_status <> -1
BEGIN
--add Date_Created if it does not exist.
IF NOT EXISTS(SELECT 1 FROM sys.columns where name = 'Date_Created' AND object_id = @TableID)
BEGIN
SET @sql = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD Date_Created datetime DEFAULT (getdate()) '
EXEC(@sql)
END
--add Date_Modified if it does not exist.
IF NOT EXISTS(SELECT 1 FROM sys.columns where name = 'Date_Modified' AND object_id = @TableID)
BEGIN
SET @sql = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD Date_Modified datetime '
EXEC(@sql)
END
FETCH NEXT FROM c1 INTO @SchemaName,@TableName,@IdentColumn
END
CLOSE c1
DEALLOCATE c1
*/
Lowell
June 17, 2010 at 9:15 am
Thank you, all for your valuable inputs.
@Vladan: my experience in programming against SQLServer databases has been that nullable fields are not populated with default values.
@Wayne: the datecreated column will only be triggered on AfterInsert while datemodified will only be triggered on AfterUpdate, for obvious reasons, as they cannot both be populated at the same time.
@lowell: most tables have the PK as the first column, yes, but some tables have composite PKs, made of two/three columns. Will it affect?
June 17, 2010 at 10:39 am
goodguy (6/17/2010)
Thank you, all for your valuable inputs.@lowell: most tables have the PK as the first column, yes, but some tables have composite PKs, made of two/three columns. Will it affect?
if the first column is not an identity, no trigger is generated with my version; so composite PK's would need a version like Waynes trigger to be edited manually.
Lowell
June 17, 2010 at 2:15 pm
June 17, 2010 at 2:43 pm
goodguy (6/17/2010)
Thank you, all for your valuable inputs.@Vladan: my experience in programming against SQLServer databases has been that nullable fields are not populated with default values.
...
They will be populated with the default, unless you explicitly include them in the insert.
June 17, 2010 at 9:33 pm
@michael-2: You are right, and so that solves one part of my problem, as I can now set datemodified default value to GETDATE(). Now i only have to worry about datecreated column.
June 20, 2010 at 2:18 pm
Actually, I realised the default value solution is not for datemodified but datecreated, as the default value will only apply the first time that the record being inserted/updated has no value for that column. Which is exactly what datecreated is supposed to do. But datemodified will need to be overwritten at every update, so the default value will not help here.
So, I set down to create teh triggers using the dynamic TSQL script above, and it runs without any errors when I click Execute but the output window returns, "No Column Name" and does not create a trigger on any table.
Is there some tweaking I am supposed to do on the script above?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply