Triggers

Question

Post reply

Triggers

Haxby

SSC Enthusiast

Points: 141
More actions
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

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

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

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

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

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

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]

El barto Ten Centuries Points: 1072 More actions · Answer 3

Gentlemen,

Thankyou for your replies.

Russel,

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

Kindest Regards,

Nick

Russel Loski SSCertifiable Points: 7059 More actions · Answer 4

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

Bhudev Prakash SSC-Addicted Points: 471 More actions · Answer 5

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;