February 27, 2009 at 6:42 am
Hi All
I want to create a table that is been used to Audit, The table has got Database name, table name and column name,if a user enter a database name on the table, the constraint must check whether that database exists on the server, the next one is the Column name, this time it must check if the table exits on the database and the Column name to check if the column exists on the table, how can i do this,
Please help me on this 🙂
Thanks in advance 🙂
February 27, 2009 at 7:20 am
well the way to do it is to add a check constriant on the column, and the check contraint uses a user defined function:
CREATE TABLE WHATEVER(
WHATEVERID INT,
DBNAME sysname CHECK(dbo.CheckDBName(DBNAME) =1) )
the function to check a dbname is easy, but checking a tablename and/or column name is harder...i can only see doing it via dynamic sql.
Let me ask a dumb question: if some application or trigger is going to be auditing , why do you have to make sure the table or column exists, if the audit process KNOWS the tablename and column name while it's doing it's process??
even worse, such a check constraint doesn't prevent me from dropping a table/column or database that is referenced in your audit table AFTER the data has been entered, it would just invalidate the data in your table, because the check function would change to bad tablename....
[font="Courier New"]CREATE FUNCTION CheckDBName(@dbname SYSNAME)
RETURNS INT
AS
BEGIN
DECLARE @i INT
SET @i=0
IF EXISTS(SELECT name FROM MASTER.dbo.sysdatabases WHERE name = @dbname)
SET @i = 1
ELSE
SET @i = 0
RETURN @i
END
GO
CREATE FUNCTION CheckTableName(@dbname SYSNAME,@TableName SYSNAME)
RETURNS INT
AS
BEGIN
DECLARE @sql VARCHAR(500),
@i INT
SET @sql = 'SELECT name FROM @dbname.dbo.sysobjects where name = @TableName'
SET @sql = REPLACE(sql,'@dbname',@dbname)
SET @sql = REPLACE(sql,'@TableName',@TableName)
--??how do you execute a dynamic query and use EXISTS?
--IF EXISTS(select name from master.dbo.sysdatabases WHERE name = @dbname)
-- RETURN 1
-- ELSE
--RETURN 0
END
[/font]
Lowell
February 27, 2009 at 7:45 am
Thanks Lowel
This is for the applications, we are creating a auditing for application ( to be precise its authentication), we are not going to have a front end data for the next few months, until then i am planning to have this set up on the database side, since if a user enter a wrong name then it shouldn't update on the table. :angry:, this is the reason.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply