I have two tables:
CREATE TABLE [dbo].[Katalog](
[IDKataloga] [int] NOT NULL,
[Godina] [int] NOT NULL,
CONSTRAINT [PK_Katalog] PRIMARY KEY CLUSTERED
(
[IDKataloga] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
and
CREATE TABLE [dbo].[StavkaKataloga](
[IDKataloga] [int] NOT NULL,
[Rb] [int] NOT NULL,
[GodinaKataloga] [int] NULL,
[IDProizvoda] [int] NOT NULL,
CONSTRAINT [PK_StavkaKataloga] PRIMARY KEY CLUSTERED
(
[IDKataloga] ASC,
[Rb] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StavkaKataloga] WITH CHECK ADD CONSTRAINT [FK_StavkaKataloga_Katalog] FOREIGN KEY([IDKataloga])
REFERENCES [dbo].[Katalog] ([IDKataloga])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[StavkaKataloga] CHECK CONSTRAINT [FK_StavkaKataloga_Katalog]
GO
ALTER TABLE [dbo].[StavkaKataloga] WITH CHECK ADD CONSTRAINT [FK_StavkaKataloga_Proizvod] FOREIGN KEY([IDProizvoda])
REFERENCES [dbo].[Proizvod] ([IDProizvoda])
GO
ALTER TABLE [dbo].[StavkaKataloga] CHECK CONSTRAINT [FK_StavkaKataloga_Proizvod]
GO
for this table a have a trigger:
create trigger Update_Katalog_Naziv
on Katalog
after update
as
declare @IDKataloga int
select @IDKataloga=IDKataloga from inserted
if update (Godina)
begin
update StavkaKataloga
set GodinaKataloga=(select top 1 Godina from inserted)
where IDKataloga=@IDKataloga
end
My trigger doesn't work
What I have tried:
If I make trigger like this:
create trigger Update_Katalog_Naziv
on Katalog
after update
as
declare @IDKataloga int
select @IDKataloga=IDKataloga from inserted
if update (Godina)
begin
alter table StavkaKataloga disable trigger Update_StavkaKataloga_GodinaKataloga
update StavkaKataloga
set GodinaKataloga=(select top 1 Godina from inserted)
where IDKataloga=@IDKataloga
alter table StavkaKataloga enable trigger Update_StavkaKataloga_GodinaKataloga
end
the trigger is working. Any explanation why this works with disable and enable rows?
Also is there a solution without those rows for this?