IF nested in TRIGGER SQL

Question

IF nested in TRIGGER SQL

Navigation

#1 by (2 votes)

4

I must make a trigger that does not allow to alter tables that are registered within a specific table.

For this, what I was thinking is to make an IF that asks if the name of the base, the schema and the name of the table are housed in that table.

Here's part of the code:

IF @DB IN (SELECT [DB] FROM Management.deployer.CriticalTables) 
    AND @SCHEMA IN (SELECT [SCHEMA] FROM Management.deployer.CriticalTables)
        AND @NAME IN (SELECT [NAME] FROM Management.deployer.CriticalTables)

(The variables are all declared) The question is that for example, if inside the table I have:

BD:      schema:    tabla:
manag      TM       prueba
manag      TM       prueba2
manag      TD       prueba

When modifying the first table (manag.TM.test), the trigger does not jump and the table is modified.

Any solution?

PS: I also tried the following

IF @DB IN (SELECT [DB] FROM Management.deployer.CriticalTables) 
    BEGIN
    IF @SCHEMA IN (SELECT [SCHEMA] FROM Management.deployer.CriticalTables)
        BEGIN
        IF @NAME IN (SELECT [NAME] FROM Management.deployer.CriticalTables)
            BEGIN
            PRINT 'ERROR'
        END
    END
END

sql sql-server

asked by Mati Borda Bossana 03.08.2016 в 02:45

source

1 answer

Upload images to the index with MVC 4 C # Insert 'N' times of records with the same Input

score 2 · Accepted Answer

The solution is not a nested if, since you must find an exact match of the three conditions, for example:

if exists (select 1 
             from Management.deployer.CriticalTables
            where DB = @DB
              and SCHEMA = @SCHEMA
              and NAME = @NAME)
begin
  print 'la tabla SI existe en Management.deployer.CriticalTables';
else
  print 'la tabla NO existe en Management.deployer.CriticalTables';