August 12, 2009 at 7:21 am
Dear All,
I have this 3 tables:
Table1:
CREATE TABLE [dbo].[uitgifteregel](
[ouitgiftenr] [int] NULL,
[oartikelnr] [int] NULL,
[oaantal] [int] NULL
) ON [PRIMARY]
Table2:
CREATE TABLE [dbo].[voorraad](
[oartikelnr] [int] NULL,
[oaantal] [int] NULL
) ON [PRIMARY]
Table3:
CREATE TABLE [dbo].[instellingen](
[oinstelnr] [int] NOT NULL,
[oomschrijving] [varchar](500) NULL,
[owaarde] [int] NULL,
CONSTRAINT [PK_instellingen] PRIMARY KEY CLUSTERED
(
[oinstelnr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I also have this trigger:
ALTER TRIGGER [dbo].[Opdr4a]
ON [dbo].[uitgifteregel]
FOR INSERT
AS
BEGIN
IF EXISTS(SELECT [voorraad].[oaantal]
FROM [voorraad]
INNER JOIN INSERTED
ON [voorraad].[oartikelnr]=INSERTED.[oartikelnr]
WHERE [voorraad].[oaantal] <> 0)
BEGIN
raiserror ('Invalid Insert; s non zero value for [oartikelnr] exists for one or more of the items isnerted.' ,16,1)
rollback tran
END --if condition
END
Now i have to expend this trigger with a second check/control:
If the 'owaarde' of table 'instellingen' is not '0' then it has to raise the error.
Can you help me with this?
Thanks.
August 12, 2009 at 7:43 am
This is the way I like to handle it..
ALTER TRIGGER [dbo].[Opdr4a]
ON [dbo].[uitgifteregel]
FOR INSERT
AS
BEGIN
DECLARE @FailFlag tinyint
SET @FailFlag = 0
IF EXISTS(SELECT [voorraad].[oaantal]
FROM [voorraad]
INNER JOIN INSERTED
ON [voorraad].[oartikelnr]=INSERTED.[oartikelnr]
WHERE [voorraad].[oaantal] 0)
BEGIN
raiserror ('Invalid Insert; s non zero value for [oartikelnr] exists for one or more of the items isnerted.' ,16,1)
SET @FailFlag = 1 -- rollback tran
END --if condition
-- Additional conditions that you want checked
-- Decide on the rollback here..
IF ( @FailFlag = 1 )
BEGIN
ROLLBACK TRAN
END
END
GO
CEWII
August 12, 2009 at 8:11 am
Hi Elliot,
Thanks for replay, bud i don't see any actions with the table 'instellingen' in you code 😉
August 12, 2009 at 1:27 pm
I setup the basis for you to add additional checks 1 or 100 it doesn't matter.
At the point it says:
-- Additional conditions that you want checked
Add an addition check, raise your error, set the fail flag, and let it continue on to the next check. When it hits the bottom it will read the fail flag and if it was changed to 1 by ANY of the checks it will do the rollback.
Since your check is not clearly stated in the post I can't write the code for you, but the basis I have shown will work for any number of checks.
CEWII
August 12, 2009 at 5:20 pm
There is no need for a trigger at all here. In fact triggers become quite a nuisance over time as systems evolve, although they have their place. In your case rather have a look at declaring constraints on the individual columns of your tables and SQL will do the checking for you without you having to add extra code. Have a look at BOL and search for CONSTRAINT and then come back if you need some extra help.
Oh, and why are almost all columns in your tables NULLable? I'm sure there is no reason for that really. Then also consider at least having a PK and clustered index on the uitgifteregel and voorraad tables. Plus FKs for those two tables as well. From your DDL statements I can't really determine which table of the two is the parent and which is the child, so I can give no guidance on that one.
Regards,
Jan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply