March 5, 2010 at 4:45 am
Hi,
Is there a way to create a Trigger on 2 tables?I want to fire a trigger if tableA (or/and) Table B are updated and insert few columns from these two tables into an Audit Table.
March 5, 2010 at 5:04 am
You can't have one trigger for two tables, but you should be able to use two (sligtly different) triggers on the two tables.
Perhaps something like this:
create trigger myTableATrigger on TableA
for update
as
-- Save data from TableA
insert into myChangeLog
select ...
from deleted
where ...
-- Save corresponding data from TableB
insert into myChangeLog
select ...
from TableB inner join
deleted
where ...
GO
create trigger myTableBTrigger on TableB
for update
as
-- Save data from TableB
insert into myChangeLog
select ...
from deleted
where ...
-- Save corresponding data from TableA
insert into myChangeLog
select ...
from TableA inner join
deleted
where ...
GO
/Markus
March 5, 2010 at 5:12 am
Trigger to update multiple tables
This is an update from a prior post. I thought this would be a simple
trigger script but it turned out to be more difficult than originally
thought. Basically my dilemma is that I have inherited an application that
calculates taxes on a transaction using customized code that is compiled and
I have no access to it. I now have to correct it using some "backdoor" logic
in SQL. I have three tables that I must access, an Invoice Header, Invoice
Detail and Tax Table. I believe I must cycle through the Invoice Detail,
update the appropriate lines, then feed the updates back up to the Invoice
Header. I have, with the help of others here, written a couple of triggers
(one of which has 3 cursors embedded in it) to accomplish this. I know this
is inefficient since every time I insert a record in the detail table it runs
through it multiple times. This code works but it is not very convenient..
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[xsmServCall](
[AmountFRTM] [float] NOT NULL,
[AmountLabor] [float] NOT NULL,
[InvoiceAmount] [float] NOT NULL,
[InvoiceStatus] [char](1) NOT NULL,
[ServiceCallID] [char](10) NOT NULL,
[TaxAmt00] [float] NOT NULL,
[TaxFRTM] [float] NOT NULL,
[TaxLabor] [float] NOT NULL,
[TaxTot] [float] NOT NULL,
[TxblAmt00] [float] NOT NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[xsmServDetail](
[DetailType] [char](1) NOT NULL,
[ExtPrice] [float] NOT NULL,
[ServiceCallID] [char](10) NOT NULL,
[TaxAmt00] [float] NOT NULL,
[TaxID00] [char](10) NOT NULL,
[TxblAmt00] [float] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
create table [dbo].[xslstaxnewcalc](
Groupid [char] (10),
MatlTax [char] (1),
LaborTax [char] (1),
TaxRate [float]
) ON [PRIMARY]
GO
INSERT INTO xslstaxnewcalc Values ('S-NYDE1221','Y','Y',8.0)
INSERT INTO xsmServCall Values (56.50, 100.00, 190.00, 'O', 'OTR0096806',
12.00, 5.00, 7.50, 22.00, 185.00)
INSERT INTO xsmServDetail Values ('L', 90.00, 'OTR0096806', 7.20,
'S-NYDE1221', 90.00)
INSERT INTO xsmServDetail Values ('M', 18.22, 'OTR0096806', 1.46,
'S-NYDE1221', 18.22)
INSERT INTO xsmServDetail Values ('M', 37.90, 'OTR0096806', 3.04,
'S-NYDE1221', 37.90)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger xsmservdetail_update ON xsmServDetail for insert, update
as
UPDATE xsmServDetail
SET txblamt00 = CASE WHEN A.DetailType = 'M' AND B.MatlTax = 'Y' AND
B.LaborTax = 'N'
THEN round((A.extprice*.65),2)
WHEN A.DetailType = 'M' AND B.MatlTax = 'Y' AND B.LaborTax = 'Y'
THEN round(extprice,2)
WHEN A.DetailType = 'M' AND B.MatlTax = 'N' and B.LaborTax = 'N'
THEN 0
WHEN A.DetailType = 'M' AND B.MatlTax = 'N' and B.LaborTax = 'Y'
THEN round((A.extprice*.35),2)
WHEN A.DetailType = 'L' AND B.LaborTax = 'Y' AND
B.MatlTax = 'Y'
THEN round(A.extprice,2)
WHEN A.DetailType = 'L' AND B.LaborTax = 'Y' AND B.MatlTax = 'N'
THEN round((A.extprice*.35),2)
WHEN A.DetailType = 'L' AND B.LaborTax = 'N' AND B.MatlTax = 'Y'
THEN round((A.extprice*.65),2)
WHEN A.DetailType = 'L' AND B.LaborTax = 'N' AND B.MatlTax = 'N'
THEN 0
END, taxamt00 = round(((txblamt00) * (b.taxrate/100)),2)
FROM xsmServDetail AS A
JOIN xslstaxnewcalc AS B
ON A.TaxID00 = B.groupid where a.servicecallid in (select servicecallid
from inserted) and a.extprice > 0 ;
create trigger xsmservcall_update on xsmServDetail for insert, update
as
Declare @smservcallid varchar(30),
@amtfrtm float,
@taxfrtm float,
@laboramt float,
@taxlabor float,
@txblamt float,
@taxtot float
Declare acur Cursor For
select a.servicecallid, sum(a.extprice) as amtfrtm, sum(a.taxamt00) as
taxfrtm from xsmServDetail a
join inserted b on a.servicecallid = b.servicecallid
where a.detailtype = 'm'
group by a.servicecallid
Open acur
Fetch Next From acur INTO @smservcallid, @amtfrtm, @taxfrtm
WHILE (@@FETCH_STATUS = 0)
BEGIN
Update xsmServCall set AmountFRTM = @amtfrtm, taxfrtm = @taxfrtm where
xsmServCall.servicecallid = @smservcallid
Fetch Next From acur INTO @smservcallid, @amtfrtm, @taxfrtm
END
Close acur
Deallocate acur
declare bcur cursor for
select a.servicecallid, sum(a.extprice) as laboramt, sum(a.taxamt00) as
taxlabor from xsmServDetail a
join inserted b on a.servicecallid = b.servicecallid
where a.detailtype = 'l'
group by a.servicecallid
open bcur
Fetch Next From bcur INTO @smservcallid, @laboramt, @taxlabor
WHILE (@@FETCH_STATUS = 0)
BEGIN
Update xsmServCall set AmountLabor = @laboramt, taxlabor = @taxlabor where
xsmservcall.servicecallid = @smservcallid
Fetch Next From bcur INTO @smservcallid, @laboramt, @taxlabor
END
Close bcur
Deallocate bcur
declare ccur cursor for
select a.servicecallid, sum(a.txblamt00) as txblamt, sum(a.taxamt00) as
taxtot from xsmServDetail a
where exists (select servicecallid from inserted)
group by a.servicecallid
open ccur
Fetch Next From ccur INTO @smservcallid, @txblamt, @taxtot
WHILE (@@FETCH_STATUS = 0)
BEGIN
Update xsmServCall set txblamt00 = @txblamt, taxamt00 = @taxtot, taxtot =
@taxtot,
InvoiceAmount = (amountfrtm + amountlabor + taxamt00) where
xsmServCall.servicecallid = @smservcallid
FETCH NEXT FROM ccur INTO @smservcallid, @txblamt, @taxlabor
END
Close ccur
Deallocate ccur
This does update the xsmservcall table with the right values. I am just
looking for a more efficient manner.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply