Re: Can I Pass a Table Name as a Parameter?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Charles.

This has the sneeky smell of business logic making its way into SQL...

I'm not even sure a check constraint is the right way to go. Transactions
should be as short as possible and having anything such as BLL to lengthen
them risks SQL nightmares to put it in a non technical way.

Surely any rule you create writing a trigger can be replicated within a SP
before the insert even occurs, either using Temp Tables or dare I say it a
cursor. Booo hisss......

If you have more info on exactly what you are doing and the mechanism
(ADO.NET, Bulk insert) ??? I may be able to help more.



"Charles" <blank@xxxxxxxxxxx> wrote in message
news:eHXgxcu2JHA.1716@xxxxxxxxxxxxxxxxxxxxxxx
Hi Paul

The reason I want to use a trigger is so that I can check other rows in
the table to ensure that the row(s) being inserted don't conflict with any
existing rows. It is not a straight forward rule that I need to apply, but
it is the same rule for several tables. I don't think I can do that from a
check constraint, but I am prepared to be wrong if there is a better way.

Charles


"Paul" <paulriley@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:eNzEIMu2JHA.4416@xxxxxxxxxxxxxxxxxxxxxxx
There is a simple reason not to use dynamic SQL.

SECURITY.

I would also question your use of Triggers. They are often a serious
source of bugs and locking issues, and I guarantee 99.9% of the time
there is a better solution.


"Charles" <blank@xxxxxxxxxxx> wrote in message
news:uc26wGt2JHA.4632@xxxxxxxxxxxxxxxxxxxxxxx
Hi Dave

Thanks for the reply. I realised after I had posted that the dynamic-SQL
solution would probably be suggested, but you have also answered the
question, I think, by saying that it is the _only_ way.

I have read Erland's article on this subject before, and take on-board
the reasons for avoiding dynamic-SQL if possible.

I have re-read his bit about "Common Cases when to (Not) Use Dynamic
SQL: SELECT * FROM @tablename", and don't entirely agree. I think there
are still occasions when, despite the fact that all tables are different
entities, the definition and purpose of the table is not unique, by
design. The only difference is that the table name and a couple of
column names differ, but the rules governing what is legal in the table
are common. In such a case it would seem much more efficient, and less
error-prone to be able to call common code in a trigger or check
constraint.

Perhaps Erland will reply and I can have the discussion with him too.

Cheers

Charles


"Dave Ballantyne" <symlink@no5p323mmer> wrote in message
news:uUOucts2JHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
Basically , no. But yes with dynamic SQL

See this link for more info :
http://www.sommarskog.se/dynamic_sql.html

--
Dave Ballantyne
http://sqlandthelike.blogspot.com/
Charles wrote:
Hi all

I realise this doesn't work as I have written it, but it demonstrates
what I would like to be able to do:

declare @TableName varchar(MAX)
set @TableName = 'MyTable'

select *
from @TableName

I am writing a trigger for a table insert, and want to put the actual
detail in a function. This function would be called from many triggers
because there are many tables that require this type of check.

Is there a way in which I can pass a table name (and, indeed, column
name) to a function so that the actual code is then generic?

It seems to me that this is not an uncommon thing to want to do, but I
haven't found any reference yet. I will continue to look, but if
anyone has a quick answer I would be most grateful.

TIA

Charles








.

Relevant Pages

  • Re: Can I Pass a Table Name as a Parameter?
    ... if inserted and deleted were accesible to a UDF called from a trigger. ... I realised after I had posted that the dynamic-SQL ... I have re-read his bit about "Common Cases when to Use Dynamic SQL: ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger help please
    ... >>I have this trigger to restrict the number of students in any one class ... For checking a non-standard constraint, ... > ClassDate. ... > expression BigClassCode + ClassDate can be evaluated, ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I Pass a Table Name as a Parameter?
    ... but my understanding is that it only contains inserted records. ... -Inserted refers to the table the trigger is created on. ... I have re-read his bit about "Common Cases when to Use Dynamic ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I Pass a Table Name as a Parameter?
    ... -Inserted refers to the table the trigger is created on. ... I realised after I had posted that the dynamic-SQL solution would probably be suggested, but you have also answered the question, I think, by saying that it is the _only_ way. ... The only difference is that the table name and a couple of column names differ, but the rules governing what is legal in the table are common. ...
    (microsoft.public.sqlserver.programming)
  • Re: 2 NULLs violate Uniqueness?
    ... > UNIQUE constraint or index. ... which requires that NULLs be ignored in a unique ... > In SQL Server you can't have a subquery in a constraint so this isn't ... an indexed view or a trigger. ...
    (microsoft.public.sqlserver.programming)