sql server before insert triger (1 Viewer)

Tfa

Registered User.
Local time
Today, 03:46
Joined
Nov 29, 2016
Messages
32
well hello again after a few months
after i changed work i went from access to sql server 2016 and i am still not familiar with a few things one of that is triggers

so here is my question
i have a table named ownership
it has 4 columns
Ownership _ID
Ship_ID
Person_ID
percentage

what i want is a trigger that it wont allow a record to be inserted if the sum of percentage is over 100 for its ID

if (select sum(percentage) from ownership
where Ship_ID = @Ship_ID ) >100

raise error ="ownership can't be more than 100%"

rollback

the question is how do i get the ship ID within the trigger ?
 

Tfa

Registered User.
Local time
Today, 03:46
Joined
Nov 29, 2016
Messages
32
well i can;t use a constrain this is what i came up

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[percentage]
ON [dbo].[SHPt_Ships_Persons]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @shipid integer
declare @sum decimal(24, 6)
declare @sum1 decimal(24, 6)
declare a cursor local for
select distinct Ship_ID
from inserted
OPEN a
fetch next from a into @shipid
while @@FETCH_STATUS = 0
begin
select @sum = isnull(sum(Percentange_Owned), 0)
from dbo.SHPt_Ships_Persons
where Ship_ID = @shipid

select @sum1 = isnull(sum(Percentange_Owned), 0)
from inserted
where Ship_ID = @shipid

if @sum + @sum1 > 100
begin
raiserror('ABCDEFGHIGKLMNOPQRSTY',16,1)
rollback
end

fetch next from a into @shipid
end
close a
deallocate a


END

but it still doesn't work
 

Users who are viewing this thread

Top Bottom