Reputation: 1759
I have this trigger
CREATE TRIGGER TriggerPickup
ON dbo.Pickup
AFTER INSERT
AS BEGIN
INSERT INTO dbo.Pickup( PickupProxy,PickupHispanic,EthnCode,CategCode,AgencyID)
SELECT
Clients.ClientProxy, Clients.Hispanic, Clients.EthnCode, Clients.CategCode,Clients.AgencyID
FROM
dbo.Clients,Pickup
where Pickup.ClientID =Clients.ClientID
END
however if i submit insert statement like this. It supposed to pull data from client table and insert in in the same row in which i insert ID and date, however it creates new records and put data in them.
INSERT INTO Pickup (ClientID, PickupDate)
VALUES (13144, '2010-11-20');
it is not copping any data from clients table it just insert empty rows Any Ideas what is going on and where is my mistake in this query
Upvotes: 1
Views: 1725
Reputation: 16904
You need to use INSTEAD OF INSERT trigger or AFTER trigger with UPDATE statements
CREATE TRIGGER TriggerPickup ON dbo.Pickup
INSTEAD OF INSERT
AS BEGIN
INSERT dbo.Pickup( PickupDate,
PickupProxy, PickupHispanic, EthnCode, CategCode, AgencyID)
SELECT i.PickupDate,
c.ClientProxy, c.Hispanic, c.EthnCode, c.CategCode, c.AgencyID
FROM dbo.Clients c JOIN inserted i ON i.ClientID = c.ClientID
END
AFTER TRIGGER
CREATE TRIGGER TriggerPickup ON dbo.Pickup
FOR INSERT
AS BEGIN
UPDATE p
SET p.PickupProxy = c.ClientProxy,
p.PickupHispanic = c.PickupHispanic,
p.EthnCode = c.EthnCode,
p.CategCode = c.CategCode,
p.AgencyID = c.AgencyID
FROM inserted i JOIN dbo.Clients c ON i.ClientID = c.ClientID
JOIN dbo.Pickup p ON i.PickupID = p.PickupID
END
Upvotes: 1