Hello.

I have an another problem in connection with my previuos question:
(
http://groups.google.co.hu/group/microsoft.public.sqlserver.programming/browse_thread/thread/49dc6d8552a38042/20bf578fe8b1674d?lnk=raot#20bf578fe8b1674d
)

There is a database and every user table has two columns at the end:
Modify_vC varchar (50) NULL
Modify_Dt datetime NULL

How can I create a Trigger for _every_ user table (there are a lot)
that updates this 2 column if a record is updated?
The values must be:
suser_sname() for Modify_vC field
      and
getdate() for Modify_Dt field

There is the my "create trigger" script. I works, but only if I write
the table name in.
So I need a loop, but I read that CREATE TRIGGER have to be the first
in the script.
I made a few try but all of them failed.

So: How can I make a loop for trigger creation on user tables?
--------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET @dbname = 'dbo.foo_table'
GO

CREATE Trigger trg_Up_foo_table_LastAccess
ON @dbname FOR UPDATE
AS


SET NOCOUNT ON
Declare @int_ErrorMsgID int
Declare @int_RowCount int
Save Tran trn_LastAccess


    UPDATE [def_Modozat]
    SET Modosito_vC=SUSER_SNAME(),Modositas_Dt=GetDate()
    FROM [def_Modozat]
    INNER JOIN inserted ins
    ON [def_Modozat].ID=ins.ID


SELECT @int_ErrorMsgID=@@ERROR,@int_RowCount=@@ROWCOUNT
IF Not(@int_ErrorMsgID=0 AND @int_RowCount>0)
Begin
  Rollback Tran trn_LastAccess
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-------------------------

Thanks for help.
Fekete Bálint

Below is an example that executes a template trigger script for every user
table in the database after replacing the $(TableName) token with the actual
table name.

DECLARE
    @TableName sysname,
    @TriggerTemplate nvarchar(4000),
    @CreateTriggerStatement nvarchar(4000)

SET @TriggerTemplate =
'CREATE TRIGGER trg_Update_$(TableName)_LastAccess
ON [$(TableName)] FOR UPDATE
AS

SET NOCOUNT ON
Declare @int_ErrorMsgID int
Declare @int_RowCount int
Save Tran trn_LastAccess

UPDATE [dbo].[$(TableName)]
SET Modosito_vC=SUSER_SNAME(),Modositas_Dt=GetDate()
FROM [dbo].[$(TableName)]
INNER JOIN inserted ins
ON [def_Modozat].ID=ins.ID

SELECT @int_ErrorMsgID=@@ERROR,@int_RowCount=@@ROWCOUNT
IF Not(@int_ErrorMsgID=0 AND @int_RowCount>0)
Begin
  Rollback Tran trn_LastAccess
End
'
--execute script for each table
DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_TYPE = 'BASE TABLE' AND
        OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

OPEN TableList
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM TableList INTO @TableName
    IF @@FETCH_STATUS = -1 BREAK
    SET @CreateTriggerStatement =
    REPLACE(@TriggerTemplate, '$(TableName)', @TableName)
    RAISERROR('Createing trigger for table %s', 0, 1, @TableName) WITH
NOWAIT
    EXECUTE(@CreateTriggerStatement)
END
CLOSE TableList
DEALLOCATE TableList

I have an another problem in connection with my previuos question:
(
http://groups.google.co.hu/group/microsoft.public.sqlserver.programming/browse_thread/thread/49dc6d8552a38042/20bf578fe8b1674d?lnk=raot#20bf578fe8b1674d
)

There is a database and every user table has two columns at the end:
Modify_vC varchar (50) NULL
Modify_Dt datetime NULL

How can I create a Trigger for _every_ user table (there are a lot)
that updates this 2 column if a record is updated?
The values must be:
suser_sname() for Modify_vC field
      and
getdate() for Modify_Dt field

There is the my "create trigger" script. I works, but only if I write
the table name in.
So I need a loop, but I read that CREATE TRIGGER have to be the first
in the script.
I made a few try but all of them failed.

So: How can I make a loop for trigger creation on user tables?
--------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET @dbname = 'dbo.foo_table'
GO

CREATE Trigger trg_Up_foo_table_LastAccess
ON @dbname FOR UPDATE
AS


SET NOCOUNT ON
Declare @int_ErrorMsgID int
Declare @int_RowCount int
Save Tran trn_LastAccess


UPDATE [def_Modozat]
SET Modosito_vC=SUSER_SNAME(),Modositas_Dt=GetDate()
FROM [def_Modozat]
INNER JOIN inserted ins
ON [def_Modozat].ID=ins.ID


SELECT @int_ErrorMsgID=@@ERROR,@int_RowCount=@@ROWCOUNT
IF Not(@int_ErrorMsgID=0 AND @int_RowCount>0)
Begin
  Rollback Tran trn_LastAccess
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-------------------------

Thanks for help.
Fekete Bálint

Thank you very much, Dan!!
It works! :))

Have a nice day.
Bálint

I'm glad it helped you out.

Thank you very much, Dan!!
It works! :))

Have a nice day.
Bálint

Hi Dan!

I have an another problem with this thing.
There are a mess with the name convetions and a lot of table does not
have "ID" column.
There are different column names which has the Identity property.

So your general solution does not work with these tables. (On my test
database did :)

How can I query the Identity column for the actual table (in the loop)?
Then I would modify this line
" ON $(TableName).ID=ins.ID "
and replace the "ID" to this variable.

Plus (a stupid question):
is it possible that there are tables which has no column with Identity
property at all?

Because it seems to me. And my boss (grr) said, let's create an ID
column with Identity property on these tables... So the script sould
examine first the existense of the ID or whatever column (with Identity
property) and create one if there is no such.

Thanks,
Bálint

One method is to join to INFORMATION_SCHEMA.COLUMNS and use the
COLUMNPROPERTY function to identify the identity column.

You can specify LEFT JOIN and COALESCE so that the identity column is used
if one exists and use another column (e.g. one named "ID") when no identity
column exists.  For example:

SELECT
    t.TABLE_SCHEMA AS TableSchema,
    t.TABLE_NAME AS TableName,
    COALESCE(c.COLUMN_NAME, 'ID') AS ColumnName
FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS c ON
    c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
    c.TABLE_NAME = t.TABLE_NAME AND
    COLUMNPROPERTY(
        OBJECT_ID(
            QUOTENAME(t.TABLE_SCHEMA) +
            '.' +
            QUOTENAME(t.TABLE_NAME)),
            c.COLUMN_NAME,
            'IsIdentity') = 1
WHERE
    t.TABLE_TYPE = 'BASE TABLE' AND
    OBJECTPROPERTY(
        OBJECT_ID(
            QUOTENAME(t.TABLE_SCHEMA) +
            '.' +
            QUOTENAME(t.TABLE_NAME)),
            'IsMSShipped') = 0

However, I think it's a mistake to take this approach.  IMHO, the right
method is to make sure all tables have a primary key and then generate the
trigger script based on the defined primary key.  Keep in mind that a table
can have a composite primary key so the script will need to handle such.
Example below.

--generate triggers for all user tables with primary key
DECLARE
    @TableName sysname,
    @TableSchema sysname,
    @ColumnName sysname,
    @JoinClause nvarchar(4000),
    @TriggerTemplate nvarchar(4000),
    @JoinTemplate nvarchar(4000),
    @CreateTriggerStatement nvarchar(4000)

SET @TriggerTemplate =
'
CREATE TRIGGER trg_Update_$(TableName)_LastAccess
ON [$(TableSchema)].[$(TableName)] FOR UPDATE
AS

SET NOCOUNT ON
Declare @int_ErrorMsgID int
Declare @int_RowCount int
Save Tran trn_LastAccess

UPDATE [$(TableSchema)].[$(TableName)]
SET Modosito_vC=SUSER_SNAME(),Modositas_Dt=GetDate()
FROM [$(TableSchema)].[$(TableName)]
INNER JOIN inserted ins
ON $(JoinClause)

SELECT @int_ErrorMsgID=@@ERROR,@int_RowCount=@@ROWCOUNT
IF Not(@int_ErrorMsgID=0 AND @int_RowCount>0)
Begin
  Rollback Tran trn_LastAccess
End
'

SET @JoinTemplate =
    '$(TableName).[$(ColumnName)] = i.[$(ColumnName)]'

--execute script for each table
DECLARE TableList
    CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT
        t.TABLE_SCHEMA AS TableSchema,
        t.TABLE_NAME AS TableName
    FROM INFORMATION_SCHEMA.TABLES AS t
    WHERE
        t.TABLE_TYPE = 'BASE TABLE' AND
        OBJECTPROPERTY(
            OBJECT_ID(
                QUOTENAME(t.TABLE_SCHEMA) +
                '.' +
                QUOTENAME(t.TABLE_NAME)),
                'IsMSShipped') = 0
    OPEN TableList
    WHILE 1 = 1
    BEGIN
        FETCH NEXT FROM TableList
            INTO @TableSchema, @TableName
            IF @@FETCH_STATUS = -1 BREAK
        SET @JoinClause = NULL
        DECLARE PrimayKeyColumnList
            CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
            SELECT kcu.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON
                kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND
                kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
            WHERE
                tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                tc.TABLE_SCHEMA = @TableSchema AND
                tc.TABLE_NAME = @TableName
        OPEN PrimayKeyColumnList
        WHILE 1 = 1
        BEGIN
            FETCH NEXT FROM PrimayKeyColumnList INTO @ColumnName
            IF @@FETCH_STATUS = -1 BREAK
            IF @JoinClause IS NULL
            BEGIN
                SET @JoinClause =
                    REPLACE(@JoinTemplate, '$(ColumnName)', @ColumnName)
            END
            ELSE
            BEGIN
                SET @JoinClause = @JoinClause +
                REPLACE(' AND ' + @JoinClause, '$(ColumnName)', @ColumnName)
            END
        END
        CLOSE PrimayKeyColumnList
        DEALLOCATE PrimayKeyColumnList

        IF @JoinClause IS NULL
        BEGIN
            RAISERROR('No primary key found for table %s', 16, 1,
@TableName)
                WITH NOWAIT
        END
        ELSE
        BEGIN
            SET @CreateTriggerStatement =
                REPLACE(@TriggerTemplate, '$(JoinClause)', @JoinClause)
            SET @CreateTriggerStatement =
                REPLACE(@CreateTriggerStatement, '$(TableName)', @TableName)
            SET @CreateTriggerStatement =
                REPLACE(@CreateTriggerStatement, '$(TableSchema)',
@TableSchema)
            RAISERROR('Createing trigger for table %s', 0, 1, @TableName)
                WITH NOWAIT
            EXECUTE(@CreateTriggerStatement)
        END
END
CLOSE TableList
DEALLOCATE TableList

I have an another problem with this thing.
There are a mess with the name convetions and a lot of table does not
have "ID" column.
There are different column names which has the Identity property.

So your general solution does not work with these tables. (On my test
database did :)

How can I query the Identity column for the actual table (in the loop)?
Then I would modify this line
" ON $(TableName).ID=ins.ID "
and replace the "ID" to this variable.

Plus (a stupid question):
is it possible that there are tables which has no column with Identity
property at all?

Because it seems to me. And my boss (grr) said, let's create an ID
column with Identity property on these tables... So the script sould
examine first the existense of the ID or whatever column (with Identity
property) and create one if there is no such.

Thanks,
Bálint

Thank you, Dan!

I will write the result as I can.

Bálint

Making a table with multiple selects and possibly simple cases
help with query
How to erase hundreds of DEFAULT values?
Indexing question
Disable all constraints