database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Devel 1261 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 1261
모든 유저 테이블에 타임스탬프 트리거 생성
작성자
정재익(advance)
작성일
2004-03-26 09:18:19ⓒ
2004-03-26 09:18:42ⓜ
조회수
13,251

모든 유저 테이블에 타임스탬프 트리거를 생성한다. 이것은 두 단계로 동작하는데 첫번째 단계에서 UpdateStamp 필드에 영향을 주는 트리거를 만든 다음 두번째 단계에서는 CreateStamp 필드에 영향을 주는 트리거를 만든다.

  • 사용가능DB : SQL Server
  • 라이센스: freeware
  • 난이도: 고급

/*
 * Create TimeStamp triggers for all user defined Tables
 * This uses a two pass system. The first pass generates the Triggers that affect the UpdateStamp fields and the 
 * second pass creates the triggers that affect the CreateStamp fields.
 */
CREATE PROCEDURE CreateTriggers  AS

/*  Local Variables */
Declare @TableName NVarChar(128)
Declare @Statement NVarChar(512)
Declare @Column_Name NVarChar (128)
Declare @KeyFields NVarChar(256)
Declare @FieldName NVarChar(128)
Declare @Table_ID Int
Declare @PassCount Int

Set @PassCount = 0

WHILE(@PassCount <= 1)
Begin
	/* Cursor declaration to figure out names of all user tables */
	Declare AllTables CURSOR 
		For Select Name from sysobjects 
			Where xtype = "U"
	Open AllTables

	/* Scan the sysobjects table for names of all user tables */
	FETCH NEXT FROM AllTables Into @TableName
	While (@@Fetch_Status = 0 )
	Begin
		Print 'Checking Table ' + @TableNAME

		Print 'Finding Primary Key Fields...'
	
		Select @Table_ID = Object_ID(@TableName)

		/* Does this table have the required fields ? */
		if @PassCount = 0
			Set @FieldName = 'UpdateStamp'
		else
			Set @FieldName = 'CreateStamp'

		exec sp_columns @TABLE_NAME = @TableName, @Column_name = @FieldName

		IF (@@RowCount = 1)
		BEGIN

			/* Cursor declaration to figure out names of key fields for a specific table (swiped from sp_pkeys) */		
			Declare ThisTable CURSOR 
			FOR
				SELECT  COLUMN_NAME = convert(sysname, c.name)  
				From sysindexes i, syscolumns c, sysobjects o, syscolumns c1
				Where 
					o.id = @Table_ID and 
					o.id = c.id and 
					o.id = i.id and 
					(i.status & 0x800) = 0x800 and 
					c.name = index_col (@TableName, i.indid, c1.colid) and 
					c1.colid <= i.keycnt and 
					c1.id = @table_id	
	
			Open ThisTable

			/* Generate SQL string that will create the trigger which creates the timestamps */
			Set @Statement = ''		
			if exists (select * from sysobjects where id = object_id(N'dbo.' + @FieldName+'_' + @TableName) and OBJECTPROPERTY(id, N'IsTrigger') = 1)
			BEGIN
				Set @Statement = 'drop trigger dbo.' + @FieldName+'_' + @TableName  + char(13)
				Print char(13)
				Print @Statement
				Execute sp_executesql  @Statement  
				Print char(13)
			END
		

			Set @Statement = 'Create Trigger dbo.' + @FieldName+'_'  + @TableName + ' ON ' + @TableName + char(13) 
			Set @Statement = @Statement + ' FOR '
			IF @PassCount = 0
				Set @Statement = @Statement + ' Update ' 
			else
				Set @Statement = @Statement + ' Insert  ' 
			Set @Statement = @Statement + ' AS ' + char(13) 
			Set @Statement = @Statement + ' BEGIN ' + char(13) 
			Set @Statement = @Statement + '     UPDATE ' + @TABLENAME + char(13) 
			Set @Statement = @Statement + '     SET ' + @FieldName + ' = GetDate() ' + char(13) 

			/* scan the system tables to build a list of primary keyfield names */
			Set @KeyFields = ""
			FETCH NEXT FROM ThisTable into @COLUMN_NAME
			WHILE (@@FETCH_STATUS= 0)
			BEGIN
				IF @KeyFields = ""
				BEGIN
					Set @KeyFields = @COLUMN_NAME
					Set @Statement = @Statement + '     WHERE '  + @COLUMN_NAME +' in (SELECT ' + @COLUMN_NAME +  ' FROM INSERTED)' + char(13) 
				END		
				ELSE
				BEGIN		
					Set @KeyFields = @KeyFields + ", " + @COLUMN_NAME
					Set @Statement = @Statement + '     AND '  + @COLUMN_NAME +' in (SELECT ' + @COLUMN_NAME +  ' FROM INSERTED)' + char(13) 
				END

				FETCH NEXT FROM ThisTable into @COLUMN_NAME
			END

			Print 'Keyfields are :- ' + @KeyFields

			Close ThisTable
			Deallocate ThisTable

			Set @Statement = @Statement + ' END' + char(13)
			Print char(13)
			Print @Statement

			If @KeyFields != ""
				Execute sp_executesql  @Statement  
			Print char(13)
		END

		FETCH NEXT FROM AllTables Into @TableName
	END
	Set @PassCount = @PassCount + 1

	Close AllTables
	Deallocate AllTables

End

[Top]
No.
제목
작성자
작성일
조회
1271SQL 서버의 디바이스 정보를 알려주는 스크립트
정재익
2004-03-31
11596
1261모든 유저 테이블에 타임스탬프 트리거 생성
정재익
2004-03-26
13251
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2019 DSN, All rights reserved.
작업시간: 0.069초, 이곳 서비스는
	PostgreSQL v11.5로 자료를 관리합니다