Consider the following table
CREATE TABLE Attributes( id int identity(1,1),
name varchar(100),
mask bigint)
Now, attribute mask will be a bit mask of the attribute name. Consider the
data:
id name mask
-- -- --
1 foo 1
2 bar 2
3 mann 4
4 frau 8
5 kein 16
6 alles 32
How do I put a trigger on this table, or something so that I do not have to
worry about the mask when I insert data? Inserting 1 record at a time would
be no problem, just get the MAX of mask and double it. But how to handle
two or more inserts at a time? Should I use an instead-of-trigger?
Sorry about the poor english.
Dieter"Dieter Katzenland" <deiter@.rrtc.com> wrote in
news:#e8DYffjDHA.2964@.tk2msftngp13.phx.gbl:
> How do I put a trigger on this table, or something so that I do not
> have to worry about the mask when I insert data? Inserting 1 record
> at a time would be no problem, just get the MAX of mask and double it.
> But how to handle two or more inserts at a time? Should I use an
> instead-of-trigger?
hi,
in this case it would be enough to let the mask empty on inserting and then
use an AFTER INSERT Trigger for calculating the mask.
--
best regards
Peter Koen
--
MCAD, CAI/R, CAI/S, CASE/RS, CAT/RS
http://www.kema.at|||Assuming that your multi-row INSERT originates from a table or query:
CREATE TABLE foo (name VARCHAR(10) PRIMARY KEY)
INSERT INTO foo VALUES ('Alpha')
INSERT INTO foo VALUES ('Beta')
INSERT INTO Attributes (id, name, mask)
SELECT COUNT(*)+
(SELECT MAX(id) FROM attributes),
A.name,
POWER(2,COUNT(*))*
(SELECT MAX(mask) FROM attributes)
FROM foo AS A
JOIN foo AS B
ON A.name >= B.name
GROUP BY A.name
--
David Portas
--
Please reply only to the newsgroup
--
Showing posts with label attribute. Show all posts
Showing posts with label attribute. Show all posts
Monday, March 12, 2012
Wednesday, March 7, 2012
IDENTITY Column!
Hi all,
I have noticed when i export data, that any resulting tables created don't
seem to include the IDENTITY attribute on the necessary columns.
These attributes are defined in the source tables, but don't carry over to
tables resulting from an export.
Can someone fill me in on what is happening there?
Is there something i could be missing?
Cheers,
AdamAdam
create table source
(
col1 int identity(1,1) not null primary key,
col2 char(1)
)
insert into source (col2) values ('a')
--create a destination table
select * into destination from source
--you will see that an identity property exists but primary key constraint
doesn't
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:exS9CWHAGHA.3372@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have noticed when i export data, that any resulting tables created don't
> seem to include the IDENTITY attribute on the necessary columns.
> These attributes are defined in the source tables, but don't carry over to
> tables resulting from an export.
> Can someone fill me in on what is happening there?
> Is there something i could be missing?
> Cheers,
> Adam
>|||By which method do you export data?|||By which method do you export data?
I have noticed when i export data, that any resulting tables created don't
seem to include the IDENTITY attribute on the necessary columns.
These attributes are defined in the source tables, but don't carry over to
tables resulting from an export.
Can someone fill me in on what is happening there?
Is there something i could be missing?
Cheers,
AdamAdam
create table source
(
col1 int identity(1,1) not null primary key,
col2 char(1)
)
insert into source (col2) values ('a')
--create a destination table
select * into destination from source
--you will see that an identity property exists but primary key constraint
doesn't
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:exS9CWHAGHA.3372@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have noticed when i export data, that any resulting tables created don't
> seem to include the IDENTITY attribute on the necessary columns.
> These attributes are defined in the source tables, but don't carry over to
> tables resulting from an export.
> Can someone fill me in on what is happening there?
> Is there something i could be missing?
> Cheers,
> Adam
>|||By which method do you export data?|||By which method do you export data?
Subscribe to:
Posts (Atom)