Creating A Trigger

Hallo,
I am new on this forum but I have an urgent problem. How can I

1. Create a trigger which has the following characteristics:
It should be named reorder.
It should fire after an update of the qty column on the stock table, if the new
value of qty is <= 5.
New should be referenced as n.
The triggered action should insert the values n.itemno and current timestamp
into the reorder table,
whereby when an inventory item in the STOCK table falls below a quantity of 6, the REORDER table will have a row inserted.

Any kind of help is very much appreciated.

Quote:

Originally Posted by JKAG

Hallo,
I am new on this forum but I have an urgent problem. How can I

1. Create a trigger which has the following characteristics:
It should be named reorder.
It should fire after an update of the qty column on the stock table, if the new
value of qty is <= 5.
New should be referenced as n.
The triggered action should insert the values n.itemno and current timestamp
into the reorder table,
whereby when an inventory item in the STOCK table falls below a quantity of 6, the REORDER table will have a row inserted.

Any kind of help is very much appreciated.

hi jkag,

Check out the Trigger i have developed in sqlserver 2000.

sample tables used:
create table stock
(
itemid int identity(1,1) primary key,
prdname varchar(100),
qty int,
)

create table reorder
(
reoid int identity(1,1) primary key,
itemid int,
prdname varchar(100),
qty int,
currdate timestamp
)

create Trigger ReorderTrigg
on stock
for update
as
begin
insert into reorder(itemid,prdname,qty) select itemid,prdname,qty from stock where qty<=5
end
go

Go through the trigger and do let me know if it has solved ur query. If not also, pm me your problem. I shall try it. Gud Luck!

cheers,
jaisql

Creating a new named instance

As understand it, the only way to add another SQL Express 2005 named instance is to run SQLEXPR32.EXE again.However, when you to, right at the end, you get the following error message:

"An installation package for the product MS SQLK Server VSS Writer cannot be found.Try the installation again using a valid copy of the installation package 'SQLWriter.msi'"

So, I deleted all named instances, and the repeated the procedure and did not get this error and it installed fine. Obviously, I would not be able to delete all instances, but in this case it didn't matter.

So, here are my questions:

Question 1) Am I correct you must rerun SQLEXPR32.EXE to create a new named instance? (I think the answer is yes)

Question 2) Is there a way to rename an existing named instance?(I think the answer is no)

Question 3) The install/uninstall for SQLEXPR32.EXE is incredibly buggy. I have spend days trying to remove an named instance with no succes. Once your remove all named instances from Control Pannel, you stll have not removed SQL Express 2005.How do you remove it?We are considering other avenues because of the incredible problems with this product. Also, we inadvertantly installed SQL Express 2005 Advanced Services, when we meant to install SQL Express 2005, so we would like to uninstall Advance Serviuces and reinstall SQL Express 2005

Question 4) I am switching from the Outlook Express newsgroups to these groups. In the context of these groups, what is the difference betwee a Forum and a Newsgroup?

Thanks

Bob

hi Bob,

BobInIndy wrote:

As understand it, the only way to add another SQL Express 2005 named instance is to run SQLEXPR32.EXE again. However, when you to, right at the end, you get the following error message:

"An installation package for the product MS SQLK Server VSS Writer cannot be found. Try the installation again using a valid copy of the installation package 'SQLWriter.msi'"

you can try unpacking the SQLExpress installer and manual install SQLWriter.msi and, eventually, SQL Native Client msi as well, as this is usually the one causing problems.. but your case does not indicates it..

So, I deleted all named instances, and the repeated the procedure and did not get this error and it installed fine. Obviously, I would not be able to delete all instances, but in this case it didn't matter.

So, here are my questions:

Question 1) Am I correct you must rerun SQLEXPR32.EXE to create a new named instance? (I think the answer is yes)

yes..

Question 2) Is there a way to rename an existing named instance? (I think the answer is no)

no

[quote]

Question 3) The install/uninstall for SQLEXPR32.EXE is incredibly buggy. I have spend days trying to remove an named instance with no succes. Once your remove all named instances from Control Pannel, you stll have not removed SQL Express 2005. How do you remove it? We are considering other avenues because of the incredible problems with this product. Also, we inadvertantly installed SQL Express 2005 Advanced Services, when we meant to install SQL Express 2005, so we would like to uninstall Advance Serviuces and reinstall SQL Express 2005

some times you have to manually remove phantoms accessing file system and registry, as described in here and/or here ..

Question 4) I am switching from the Outlook Express newsgroups to these groups. In the context of these groups, what is the difference betwee a Forum and a Newsgroup?

newsgroup are another community type, usually diffused and accessed via NNTP protocol, where forums have been designed for web access, usually with additional features like link sections, tips section and the like.. anyway this one should be consider a forum, AFAIK..

regards

Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
Ivan
Ivan
Take a look at this example posted by Steve Kass long time ago
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>
|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> CREATE TABLE dupNulls (
> pk int identity(1,1) primary key,
> X int NULL,
> nullbuster as (case when X is null then pk else 0 end),
> CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
> )
> INSERT INTO dupNulls(X) VALUES (1)
> INSERT INTO dupNulls(X) VALUES (NULL)
> INSERT INTO dupNulls(X) VALUES (NULL)
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 1 WHERE pk = 2
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 2 WHERE pk = 2
> SELECT pk, X, nullbuster FROM dupNulls
> DROP TABLE dupNulls
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>
|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>

Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
IvanIvan
Take a look at this example posted by Steve Kass long time ago
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> CREATE TABLE dupNulls (
> pk int identity(1,1) primary key,
> X int NULL,
> nullbuster as (case when X is null then pk else 0 end),
> CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
> )
> INSERT INTO dupNulls(X) VALUES (1)
> INSERT INTO dupNulls(X) VALUES (NULL)
> INSERT INTO dupNulls(X) VALUES (NULL)
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 1 WHERE pk = 2
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 2 WHERE pk = 2
> SELECT pk, X, nullbuster FROM dupNulls
> DROP TABLE dupNulls
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>

Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
IvanIvan
Take a look at this example posted by Steve Kass long time ago
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> CREATE TABLE dupNulls (
> pk int identity(1,1) primary key,
> X int NULL,
> nullbuster as (case when X is null then pk else 0 end),
> CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
> )
> INSERT INTO dupNulls(X) VALUES (1)
> INSERT INTO dupNulls(X) VALUES (NULL)
> INSERT INTO dupNulls(X) VALUES (NULL)
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 1 WHERE pk = 2
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 2 WHERE pk = 2
> SELECT pk, X, nullbuster FROM dupNulls
> DROP TABLE dupNulls
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> My table on the SQL 2000 Server has a filed Id which is a (clustered)
>> primary key.
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
>> How can I manage this constraint with help of SQL Server Enterprise
>> manager direct on the table or with help of Quer Analyzer?
>> Thanks
>> Ivan
>|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> My table on the SQL 2000 Server has a filed Id which is a (clustered)
>> primary key.
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
>> How can I manage this constraint with help of SQL Server Enterprise
>> manager direct on the table or with help of Quer Analyzer?
>> Thanks
>> Ivan
>