Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables:

SQL
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

SQL
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:

SQL
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:

SQL
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?
Posted
Updated 13-Dec-21 5:14am
v2

1 solution

Looking at the trigger here
SQL
select @IDKataloga=IDKataloga from inserted

If multiple rows are updated, the inserted table contains several rows so you'd either need to handle them as a set or loop through them one-by-one.

Perhaps something like
SQL
create trigger Update_Katalog_Naziv
on Katalog
after update as
if update (Godina) begin
   alter table StavkaKataloga disable trigger Update_StavkaKataloga_GodinaKataloga

   update StavkaKataloga
   set GodinaKataloga=(select top 1 Godina from inserted)
   where IDKataloga in (select IDKataloga from inserted)

   alter table StavkaKataloga enable trigger Update_StavkaKataloga_GodinaKataloga
end

Please note that the you didn't describe what the logic should be so the converted code may not do what you actually want.
 
Share this answer
 
Comments
Nikola Milovanovic 13-Dec-21 11:28am    
@Wendelius thanks for your reply! I tried your code and it worked, but before I saw yours I tried this one

create trigger Update_Katalog_Naziv
on Katalog
after update
as
if update (Godina)
begin
update StavkaKataloga
set GodinaKataloga=(select top 1 Godina from inserted)
from dbo.StavkaKataloga s
INNER JOIN INSERTED i ON s.IDKataloga = i.IDKataloga join Katalog k on s.IDKataloga = k.IDKataloga
where k.IDKataloga = s.IDKataloga
end

I am very thankful for your solution! The only thing that matters now is that all 3 options only work with lines disable trigger and enable trigger. I still cant find a way to make any of my solutions work without those lines. Are those lines mandatory with SQL server?
Wendelius 13-Dec-21 12:14pm    
Disabling a trigger is not mandatory. Most likely the trigger is preventing you from updating the rows. What does the trigger Update_StavkaKataloga_GodinaKatalog look like?
Nikola Milovanovic 13-Dec-21 13:13pm    
Sorry for my late response:

create trigger [dbo].[Update_StavkaKataloga_GodinaKataloga]
on [dbo].[StavkaKataloga]
AFTER UPDATE
as
declare @GodinaKataloga int
select @GodinaKataloga=GodinaKataloga from inserted
if @GodinaKataloga not like (select GodinaKataloga from deleted)
begin
raiserror('Zabranjeno je direktno azuriranje GodineKataloga u StavciKataloga!', 16,1)
rollback
end

i dont want to allow the user to manualy update value in GodinaKataloga, and raise an error - Forbiden...
Wendelius 13-Dec-21 13:22pm    
Well basically you have the same kind of error here. If multiple rows are updated the select statement fails since a single variable can only hold a single value.

Another thing is the condition. In comparison you have used LIKE which is for characters but the variable is defined as int, so there's probably some kind of mismatch.

Looking at the error message in raiserror, it looks that updating the year is prohibited so in this sense you either need to change the logic in trigger you posted in the original question (preferred way) or disable the trigger when updating the data (may lead to problems).
Nikola Milovanovic 13-Dec-21 14:50pm    
You helped me a lot! When I deleted Update_StavkaKataloga_GodinaKatalog trigger, all 3 solutions are working. Thank you very much

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900