ALTER TABLE question

Hi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I want to change the nullability of some columns and add a
primary key constraint to a table. When I monitor what the
ALTER TABLE really does (using Profiler), I see strange
inserts and updates like "insert [dbo].[table] select *
from [dbo].[table]" or "update [dbo].[table] set [column] =
[column]". What are they for, and will these inserts and
updates activate any triggers/defaults/constraints defined
for a table?
I use SQL Server 2000 SP3a.
Many thanks,
Osk
Are you doing this in Enterprise Manager or Query Analyzer.
You really want to be using Query Analyzer to make these
kind of changes.
-Sue
On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>defaults/observe any constraints defined on a table?
>I want to change the nullability of some columns and add a
>primary key constraint to a table. When I monitor what the
>ALTER TABLE really does (using Profiler), I see strange
>inserts and updates like "insert [dbo].[table] select *
>from [dbo].[table]" or "update [dbo].[table] set [column] =
>[column]". What are they for, and will these inserts and
>updates activate any triggers/defaults/constraints defined
>for a table?
>I use SQL Server 2000 SP3a.
|||I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
ALTER TABLE command as far as I know. But what about
triggers/constraint - will they bey fired/observed?
Thanks,
Osk

>--Original Message--
>Are you doing this in Enterprise Manager or Query Analyzer.
>You really want to be using Query Analyzer to make these
>kind of changes.
>-Sue
>On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
|||On Thu, 20 Jan 2005 22:39:58 -0800, Osk wrote:

>I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
>ALTER TABLE command as far as I know. But what about
>triggers/constraint - will they bey fired/observed?
Hi Osk,
Easy to test, isn't it?
CREATE TABLE Test (Col1 int NULL,
Col2 int NULL)
go
CREATE TRIGGER TestTrig ON Test
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'I''m fired!'
go
ALTER TABLE Test
ALTER COLUMN Col1 int NOT NULL
go
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY (Col1)
go
DROP TABLE Test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)