Post reply

Triggers

  • December 14, 2006 at 10:33 am

    #115399

    Hi,

    I have two tables in my SQL Database.

    T1,

    Date,Time,Customer, Value,Ref

    T2

    Date,Time,Customer, Value,Ref

    I am looking for a way to update T2 when a value is inserted into T1, but the trigger would need to validate that the record does not already exist in T2.

    Could you possiblt assist me with this ?

    Thanks,

     

    Amie

  • December 14, 2006 at 10:52 am

    #678153

    You could try something like:

    if exists (select * from t2 t inner join inserted i

      on t.Customer= i.Customer

       and t.Value = i.Value)

    begin

    raiserror ('The value exists in t2', 16, 1)

    rollback transaction

    end

    The "inserted" table indicates the records that are inserted by an insert query or updated by an update query.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • December 14, 2006 at 11:02 am

    #678156

    you database design isnt right. You shouldnt be storing the same data in two different tables becasue you are likely to have problems with consistency.

    why not just add a bit column to t1 which gets set when the event that previously duplicated the row in t2 occurs.

    www.sql-library.com[/url]

  • December 14, 2006 at 12:56 pm

    #678186

    Gentlemen,

    Thankyou for your replies.

    Russel,

    Would your solution also work if an application updated the tables? 

    Kindest Regards,

    Nick

  • December 14, 2006 at 1:07 pm

    #678189

    It would if you put this in an update trigger.  You can specify that the trigger is an update trigger or insert trigger or both.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • December 15, 2006 at 4:25 am

    #678290

    Hi

    It is simply possible with Triggers as well as OUTOUT with using INSERT Statement.

    There is smaple programe of using OUTPUT Clause.
    INSERT INTO dbo.EmployeeSales 
    OUTPUT INSERTED.EmployeeID, 
           INSERTED.LastName, 
           INSERTED.FirstName, 
           INSERTED.CurrentSales,
           INSERTED.ProjectedSales
    SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.10
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

    		 

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply