locked
Using a recurive Common table expression in a Trigger RRS feed

  • Question

  • I have a table with parent child relationship. if someone updates a row which has children, I have to audit that an explict change has been made to parent row, and the child rows have been edited implicitly.

    In order to determine all children I have to use a recurive CTE.

    I stared writing something like

     

    create trigger TR on T INSTEAD OF UPDATE

    AS

    With CTE As (

        Select T.ID from T inner join deleted d on t.ID = d.ID

        UNION ALL

        Select T.ID from T

        inner join CTE C on C.ID = T.Parent_id

    )

    insert into Audit select * from CTE;

    GO

     

    But this doesn't work and it seems to me that I will have to define the CTE outside of the trigger

    But If I do that the first query in the CTE needs the row which was updated. If I define the CTE outside of the trigger I cannot use the deleted table.

     

    What should I do?


    • Moved by Jonathan KehayiasMVP Friday, May 6, 2011 1:59 PM TSQL Question (From:SQL Server Database Engine)
    Friday, May 6, 2011 1:38 PM

Answers


  • Are you saying that you ARE able to define the CTE inside the trigger?

    Yes, you can use CTE's inside a trigger.  And what you posted should work (does work for me).  For example, if I run

    use tempdb
    create table t(id int, parent_id int, other_data varchar(20));
    go
    create table audit (id int);
    go
    create trigger TR on T INSTEAD OF UPDATE
    AS
    With CTE As (
      Select T.ID from T inner join deleted d on t.ID = d.ID
      UNION ALL
      Select T.ID from T
      inner join CTE C on C.ID = T.Parent_id
    )
    insert into Audit select * from CTE;
    GO
    insert t(id, parent_id, other_data) values (1, null, 'a');
    insert t(id, parent_id, other_data) values (2, 1, 'c');
    update t set other_data = 'b' where id = 1;
    select * from audit;
    drop table t;
    go
    drop table audit;
    

    it runs without error, and puts two rows into the audit table.

    Tom

    • Marked as answer by MSDN Student Friday, May 6, 2011 5:23 PM
    Friday, May 6, 2011 3:22 PM

All replies

  • When you say it does not work, is the trigger not firing? Are you getting an error? Or is it that the data is just not in the Audit table?
    Friday, May 6, 2011 2:08 PM
  • hi,

    it's sufficient for your audit to store the row when it is parent row. The affected children can be retrieved when you inspect the audit log, and should not be stored (redundancy).

    So your trigger may look like

    CREATE TRIGGER tr_Audit ON yourTable
        AFTER UPDATE
    AS
        SET NOCOUNT ON ;
         INSERT  INTO auditTable
                SELECT  I.ID
                FROM    INSERTED I
                WHERE   EXISTS ( SELECT 1
                                 FROM   yourTable T
                                 WHERE  T.Parent_ID = I.ID ) ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Friday, May 6, 2011 2:15 PM
  • hi,

    I said, that you don't need any CTE in the trigger...


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Friday, May 6, 2011 3:11 PM

  • Are you saying that you ARE able to define the CTE inside the trigger?

    Yes, you can use CTE's inside a trigger.  And what you posted should work (does work for me).  For example, if I run

    use tempdb
    create table t(id int, parent_id int, other_data varchar(20));
    go
    create table audit (id int);
    go
    create trigger TR on T INSTEAD OF UPDATE
    AS
    With CTE As (
      Select T.ID from T inner join deleted d on t.ID = d.ID
      UNION ALL
      Select T.ID from T
      inner join CTE C on C.ID = T.Parent_id
    )
    insert into Audit select * from CTE;
    GO
    insert t(id, parent_id, other_data) values (1, null, 'a');
    insert t(id, parent_id, other_data) values (2, 1, 'c');
    update t set other_data = 'b' where id = 1;
    select * from audit;
    drop table t;
    go
    drop table audit;
    

    it runs without error, and puts two rows into the audit table.

    Tom

    • Marked as answer by MSDN Student Friday, May 6, 2011 5:23 PM
    Friday, May 6, 2011 3:22 PM
  • Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.  

    >> I have a table with parent child relationship.<<

    No. The terms “child” and “parent” are from CODASYL network databases and have nothing to do with RDBMS.  We have “referenced” and “referencing” tables. A relationship has to be among two or more tables, not just one table.

    >>  if someone updates a row which has children [sic], I have to audit that an explicit change has been made to parent [sic: referenced] row, and the child [sic: referencing] rows have been edited implicitly. <<

    Your third party audit tool will catch the log file and record this. You would never have a trigger on a table for audits; ask your lawyer and/or attorney why. It is the legal version of the same reason we do not keep the log file on the same hard disk as the schema or put an extra set of car keys in our glove copmpartment so you will not be locked out.

    >> In order to determine all children I have to use a recursive CTE. <<

    Why? We cannot see the DDL you never posted. In RDBMS, the DRI actions will cascade the changes from the referenced table to all the referencing tables. The log will record all the changes. This is NOT like a file system where you would have to do pro grammatically what you are trying to do. SQL does it declaratively.

    Your vague narrative and the lack of basic RDBMS understanding imply that you are doing this completely wrong. Do you want to try to do it right? Post basic Netiquette and get some real help; you are getting sinking kludges now and will think this is how to write SQL.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Friday, May 6, 2011 9:25 PM
  • Wow.... "the" Joe Celko. I am so honored that you have taken time to reply to my question. Thank you sir.

     

    First my requirements: Any explict and implict change to a Policy must be logged in aother Policy_Audit table. We have a self reference in the audit table. this means that a Policy can have a parent policy. If an update is issued to a parent policy, the behaviour of all its children will change, and therefore we need to audit the implicit change to the child policies.

    the audit is not to be done on a file. it must be done to a table where we can generate audit reports.

    In order to implement this I wrote a "Instead Of" trigger on the Policy table where if a row is updated, I need to determine all its children. then the change to the parent row is audited in the Policy_Audit table as explict change, and all the child rows are recorded as changed implicitly.

    Here Policy actually means an active directory group which has been given access to a system.

    Here is my Code sample

     

     

    drop trigger TR
    go
    
    drop table Policy
    go
    
    drop Table Policy_Audit
    go
    
    create table Policy (
    	policy_id nvarchar(10) not null,
    	parent_policy_id nvarchar(10),
    	value nvarchar(10),
    	GSMTicket nvarchar(10)
    )
    go
    
    create table Policy_Audit (
    	id int primary key identity(1, 1),
    	policy_id nvarchar(10) not null,
    	parent_policy_id_old nvarchar(10),
    	parent_policy_id_new nvarchar(10),
    	value_old nvarchar(10),
    	value_new nvarchar(10),
    	GSMTicket nvarchar(10),
    	change_date datetime,
    	explicit_change bit,
    )
    go
    
    
    alter table Policy Add Constraint Policy_pk primary key (policy_id)
    go
    alter table Policy Add Constraint Policy_fk foreign key (parent_policy_id) references Policy(policy_id)
    go
    insert into Policy (policy_id, parent_policy_id, value, GSMTicket) values ('TOP', NULL, '', '0')
    insert into Policy (policy_id, parent_policy_id, value, GSMTicket) values ('MASTER', NULL, '', '0')
    insert into Policy (policy_id, parent_policy_id, value, GSMTicket) values ('FIRMWIDE', 'MASTER', 'SOME VALUE', '0')
    GO
    
    create trigger TR on Policy INSTEAD OF UPDATE
    AS
    	WITH MyChildValues as (
    		select p.policy_id, d.parent_policy_id, i.parent_policy_id, d.value, i.value, i.GSMTicket, Getdate() as change_date, 1 as explicit_change from Policy p
    			inner join deleted d on p.policy_id = d.policy_id
    			inner join inserted i on p.policy_id = i.policy_id
    			
    		UNION ALL
    		
    		Select p.policy_id, p.parent_policy_id, NULL, p.value, NULL, m.GSMTicket, GETDATE() as change_date, 0 as explicit_change from Policy p 
    			inner join MyChildValues m on m.policy_id = p.parent_policy_id			
    	)
    	
    	insert into Policy_Audit (policy_id, parent_policy_id_old, parent_policy_id_new, value_old, value_new, GSMTicket, change_date, explicit_change) select * from MyChildValues
    GO
    
    update Policy set value='TEST' where policy_id='MASTER'
    
    select * from Policy_Audit
    
    

     

     


    Saturday, May 7, 2011 4:40 PM