SQL Triggers and stored procedures

[MandlaT]

Hi All,I have two tables in a database and need to create a trigger after update on one table so that it updates another. may u pliz help me with this problem I have.

[DeMan]

Hi MandlaT,

Welcome to the scripts!! I will move this ost to the SQL forum for you. In future you can post there directly, by selecting the SQL forum from the "Forums" tab on the blus bar at the top of your screen.

I hope you find the information you need!

[MandlaT]

Thanks! I look forward to getting replies.

[frozenmist]

Hi Mandla,
You can have the following syntax for the trigger

create trigger trigger_name on table1
after update
as
begin
update table2 set col=<new value> [where <condition>];
end
 

Hope this helps
Cheers

[MandlaT]

Thanks, will let you know about the outcome.

[MandlaT]

Thanks, It worked but I'm getting duplicate values. how can I solve the problem?

[frozenmist]

Thanks, It worked but I'm getting duplicate values. how can I solve the problem?

Hi,
Can you give more input. May be the update statement you are using or the trigger

Cheers

[MandlaT]

Hi Thanks for answering me,
when there is an update on dstParameters it should set the trigger to update dstControlData. but am getting duplicate values. It should only accept different values on the date eg 200704 or 200705 on [dstYearMonth].
-- =============================================
-- Author: User
-- Create date: 17/05/2007
-- Description: Updating
-- =============================================
ALTER TRIGGER .[dbo].[updateData]
ON .[dbo].[dstSalesParameters]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE @data XML;

DECLARE @distributer nvarchar(15);
DECLARE @salesPeriod int;
DECLARE @salesRec int;
DECLARE @customerRec int;
DECLARE @materialRec int;
DECLARE @callSheetRec int;
DECLARE @importPriceRec int;
DECLARE @extractPriceRec int;
DECLARE @extractEquipRec int;




SET @distributer = (SELECT dstDistributer FROM inserted);
SET @salesPeriod = (SELECT SalesPeriod FROM inserted);
SET @salesRec = (SELECT ExtractSaleRec FROM inserted);
SET @customerRec = (SELECT ExtractCustomerRec FROM inserted);
SET @materialRec = (SELECT ExtractMaterialRec FROM inserted);
SET @callSheetRec = (SELECT ExtractCallSheetRec FROM inserted);
SET @importPriceRec= (SELECT ImportPriceRec FROM inserted);
SET @extractPriceRec = (SELECT ExtractPriceRec FROM inserted);
SET @extractEquipRec = (SELECT ExtractEquipmentRec FROM inserted)


--FROM dstSalesParameters

INSERT [dbo].[dstControlData]
(
--[dstControlID],
[dstAgentCode],
dstYearMonth,
dstSalesRec,
dstCustomerRec,
dstMaterialRec,
dstCallSheetRec,
dstIPriceRec,
dstEPriceRec,
dstEquipmentRec
)
VALUES
(
@distributer,
@salesPeriod,
@salesRec,
@customerRec,
@materialRec,
@callSheetRec,
@importPriceRec,
@extractPriceRec,
@extractEquipRec
);





END

[MakingDBA]

Dear MandlaT,

I am not totally clear what you're asking but maybe you want to do a select on dstControl for the Yeardate you are about to insert so that you do so only if it doesnot exist.

Regards
MakingDBA