Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Forcing integrity between tables and procedures

Jan 17 2008 1:37PM by Madhivanan   

You can force integerity between tables and views with schemabinding when you create views so that tables cant be dropped until views that bind on tables are dropped. Unfortunately it is not possible to force this integrity between tables and procedures. Tables can be dropped though there are procedures referencing them But if you use SQL Server 2005 or higher version, it is possible with DDL trigger which is one of finest features available

 
CREATE TABLE test_table(code int, names varchar(100))
GO
CREATE TRIGGER alert_me
ON DATABASE 
FOR DROP_TABLE 
AS
declare @table_name varchar(100)
select @table_name= right(ddl,charindex(' ',reverse(ddl))-1) from
(
select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(100)') as DDL
) as t 

If exists
(
select * from information_schema.routines
where routine_definition like '% '+@table_name+'%'
)
beginRaiserror('One or more procedures depend on this table and cant be dropped' , 16, 1) Rollbackend

GO 

create procedure test_spasselect * from test_table

GO
drop table test_table

Now you would get error as test_sp is referencing the table

Tags: t-sql, sql_server, procedure, integrity,


Madhivanan
3 · 40% · 12936
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Forcing integrity between tables and procedures" rated 5 out of 5 by 1 readers
Forcing integrity between tables and procedures , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]