SQL triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL triggers

Author

Topic

uzipaz
Starting Member

1 Post

Posted - 2011-05-01 : 13:54:43

Hello Everybody,
I have table in my oracle database where store the data regarding property, the primary key is an ID that is an integer itself. So, whenever I want to insert a tuple in this table, I want its ID to be incremented before its inserted. So, I have made a table in my database using TOAD software. After this I wrote the following statements in the S.Q.L Editor.

CREATE SEQUENCE property_id_incrementer start with 0 increment by 1 minvalue 0;

CREATE TRIGGER increment_property_id
ON PROPERTY
BEFORE INSERT
AS
BEGIN
PROPERTY.PROPERTY_ID=property_id_incrementer.nextval // I am not sure if this statement is correct!
END
GO;

The sequence is created successfully, but the trigger won't, I am sure the assignment is not correct. Should I use the 'INSERT INTO TABLE' statement here?

Another question, suppose I have five tuples in that table with ID's ranging from 1 to 5. Now, I delete the 3rd tuple with ID=3, the result would be four tuples with IDs=1,2,4,5. However, this should'nt happen this way, the IDs should be 1,2,3,4. Right?

How will handle this case?

Thanks.

Hello world...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-01 : 14:22:31

This is a Microsoft SQL Server forum. While there are some people here who are familiar with Oracle, most are SQL Server people.
You'll likely get far better and faster replies it you post on an Oracle specific forum.

Maybe http://www.dbforums.com

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-01 : 14:32:53

Yes you should use an insert statement.
Just use the sequence value to insert into that column.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources