Antares ??
-
I have a table that includes a "self-join".
The Identity column(id_Account) is joined to another column in the same table called id_AccountToDebit. THis is all fine and dandy - I have a Foreign Key set up to ensure RI but I have a problem.
For a number of reasons - I want the "id_AccountToDebit" column to be NOT NULL. Ideally, by default when a new row is entered, the new Identity "id_Account" ID would also be plugged into the "id_AccountToDebit" ID.
However - SQL server will not allow for his to happen by default. It seems to me that an "InsteadOf" trigger is required for such a thing but I'm unable to get this to function.
AM I stuck with a NULL column for the "id_AccountToDebit" and using an AFTER Trigger to populate that column by using something like this (this works for the moment but the Debit column MUST be NULL):
UPDATE
Account
SET
ID_AccountToDebit = I.id_Account
FROM
INSERTED I
JOIN Account AC
ON I.id_Account = AC.id_Account
Any suggestions appreciated.
- B
-
Either you will need to enforce NOT NULL thru a trigger, checking field on INSERT in trigger, do the logic client side or use a Stored Procedure with the logic and let the client hit it instead of the table directly (preferred method). Unfortunately the field NOT NULL is evaluated before the trigger can fire.
-
You could have a dummy record with a pk of -1 and use -1 as a default value.
create table simon(col1 int not null primary key, col2 int not null default -1 )
alter table simon add constraint fk1 foreign key (col2) references simon(col1)
go
create trigger Fredt on simon instead of insert
as
begin
insert into simon select col1, col1 from inserted
end
go
insert into simon (col1) values (1)
go
select * from simon
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Thanks guys..
Antares:
I think the "Instead Of" trigger fired BEFORE and constraints are avaluated - Need to test but pretty sure.
Simon - I really want the PK column to be a Serial Identity column if at all possible.
In a nutshell - can I get a new Identity value from the Inserted table when using an "Instead Of" Trigger ??
-
You can have col1 in my example as an identity. try it
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Thanks Simon.
Totally side/personal note.
Why do Brits feel it necessary to use their names as much as possible in written expression?
... insert into simon ...
... select * from simon ...
I used to work with a Brit(also named Simon) who was a spoiled rich brat and I saw the name "Simon" so many times I wanted to puke - so I'm inherently adverse to such a thing.
Perhaps it's simply a common occurance to refer to oneself when using examples in the UK. Can't you use Table1 / Table2 or Parent / Child ?
Blah, Blah - just brought up old bad memories 🙂
-
Still having trouble.
Seems to have to do with the IDENTITY_INSERT.
The Trigger is firing, but the Identity is not "really" created for/by the Insert trigger.
create table Table1
(
ID int identity(1,1) not null primary key,
SomeText varchar(10) not null,
LinkID int not null default -1
)
alter table Table1 add constraint FKLink foreign key (LinkID) references Table1(ID)
go
create trigger InsertTrigger on Table1 instead of insert as
begin
insert into Table1 (ID, SomeText, LinkID) select ID, SomeText + 'XXX', LinkID from inserted
end
go
SET IDENTITY_INSERT Table1 ON
insert into Table1(ID, SomeText) VALUES(-1, 'A') --default values
go
select * from Table1
/*
drop table Table1
*/
-
-- Hope this helps a bit
Set NoCount on
GO
Create table Table1 (ID int identity(1,1) not null primary key, SomeText varchar(10) not null, LinkID int not null default -1)
go
Create Trigger InsertTrigger on Table1 for Insert as
Update Table1
Set LinkId=Table1.[Id]
From Inserted
Where Table1.Id=Inserted.ID
Return
GO
Create Trigger UpdateTrigger on Table1 for Update as
If (Select Count(*)
From Deleted as d
Inner Join Table1 as t
On d.Id=t.LinkId)<>
(Select Count(*) from Deleted)
Begin
Rollback Transaction
RaisError ('Invalid Entry for LinkId.',16,1)
End
Return
GO
insert into Table1(SomeText)
VALUES('A') --default values
insert into Table1(SomeText)
VALUES('B') --default values
insert into Table1(SomeText)
VALUES('C') --default values
insert into Table1(SomeText)
VALUES('D') --default values
go
Select * from Table1
GO
Update table1
Set linkid=Case When table1.Id=2 then Linkid else 10 end,
SomeText=SomeText+'t'
Where Table1.SomeText between 'A' and 'B'
go
Drop Table table1
go
Set NoCount oFF
GO
-
Should have an if UPDATE(column) in the update trigger. Only do the check if LinkId was changed.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply