How to determine what event has caused Trigger to fire

Hello all.
I wish to use a single trigger for all Update/Insert/Delete events.
In order to do so I need a way of determining which event has caused the combined trigger to fire.
Based upon the event I can then determine which tables (Inserted or Deleted) I need to reference.
So I will have something like ;

CREATETRIGGER [Test_Trigger]

ON [dbo].[Table]

AFTER INSERT,UPDATE,DELETE

and will need to do something like ;

AS

BEGIN

IF <Insert>

Do something with INSERTED

ELSE

IF <Delete>

Do something with DELETED

I dont really want to create 3 triggers for each event
Many thanks

If INSERTED is populated and DELETED is not, then it's an insert

If INSERTED is not populated and DELETED is, then it's a delete

If both are populated, it's an UPDATE

|||

Normally, there are very distinct and different actions to be taken determined by the type of TRIGGER action. And normally, it is more efficient to have separate TRIGGERs. If you have additional code, even with good IF conditions and good flow control, you are having to execute unnecessary code EVERY time there is a table action.

Consider that you want a taxi to travel from point A to point D. Every time you travel there, the taxi goes by Point B and Point C (not a straight line) -it doesn't stop, but it goes by those places also. Was that efficient? Are you wasting time? Would you have prefered to travel straight from Point A to Point D?

The exception is for creating a history or 'audit' trail. In that case, it is usually enough to handle the 'deleted' table to store the previous data state (the current data state is in the table).

|||

You can use Inserted & Deleted logical (conceptual) tables to identify the current operation.

Yes, as Arine says you can have seperate triggers for each operation. It will reduce the additional overload of your query.

The sample as follow as,

Code Snippet

Create Trigger ..

..

..

Begin

Declare @.Flag as Int;

Select Top 1 @.Flag = 1 From Inserted;

Select Top 1 @.Flag = Isnull(@.Flag, 0) + 2 From Deleted;

If @.Flag = 1

Begin

--'Insert Operation Performed'

Return;

End

If @.Flag = 2

Begin

-- 'Delete Operation Performed'

Return;

End

If @.Flag = 3

Begin

-- 'Update Operation Performed'

Return;

End

End

|||

Thank you all for your comments.
I have done it, it is essentially what DaleJ also suggested.

This is what I did ;

DECLARE @.InsCount_T int

DECLARE @.DelCount_T int

SELECT @.InsCount_T =Count(*)FROM INSERTED

SELECT @.DelCount_T =Count(*)FROM DELETED

IF @.InsCount_T > @.DelCount_T --Insert

INSERTINTO dbo.Audit

SELECT*FROM INSERTED

ELSE

IF @.InsCount_T = @.DelCount_T --Update

INSERTINTO dbo.Audit

SELECT*FROM DELETED

ELSE

IF @.InsCount_T < @.DelCount_T --Delete

INSERTINTO dbo.Audit

SELECT*FROM DELETED
I understand what Arnie says about performance, however as with most things there is a trade-off between performance and maintainability. One trigger is easier to maintain and I dont imagine that the performance hit would be that great given the code above and the fact that a trigger will fire in any case. The only extra bit are the Counts on the tables. In my case I think a minor performance hit is acceptable given the number of users, potential additions/updates/deletes and the number of databases currently on server. But thanks for the word of warning, I'll certainly bear it in mind for more high usage db's. Manivannan's code is probably a more efficient way of doing it as it does not count all of the rows.

|||

I would add the following at the top:

IF @.@.ROWCOUNT = 0

RETURN

That will prevent the TRIGGER having to check the inserted and deleted tables needlessly when the TRIGGER is fired but there is no data available. (That can happen in a number of situations, such as CONSTRAINT failure.

Personally, I consider every little piece of code to require 'clock ticks', and I want my code to execute with the minimum 'clock ticks'. That is the attitude required in order to maximize performance efficency. In checking the count of inserted/deleted, you are wasting clock ticks.

It appears that all you are doing is making copies of inserted and deleted ino the Audit table. Why bother with the checks? Just INSERT into Audit from deleted, and then INSERT into Audit from inserted. If the virtual tables are empty, nothing happens -the results are the same with a lot less code (and wasted clock ticks.)

|||

Hi Preet,

You do not need to count rows in order to prove existence. how long it will take if you insert a mass of rows, better to use operator EXISTS.

if exists(select * from inserted) and exists(select * from deleted)

...

Also, use the tip given by Arnie, because most of your comparisons will fail if no row was affected by the statement that caused the trigger to be fired.

delete dbo.t1

where 0 = 1;

The trigger will catch:

...

ELSE

IF @.InsCount_T = @.DelCount_T --Update

INSERTINTO dbo.Audit

SELECT*FROM DELETED

and as you can see, the action was a "delete" and not an "update".

AMB

|||

Thanks to Arnie & Hunchback, its always good to 'hear' another viewpoint.
Sorry Arnie, I omitted a field that I am populating in Audit. I am using 0,1,2 to indicate the type of update. So In the case of an update I am executing ;

INSERTINTO dbo.Audit

SELECT*,1FROM DELETED (not SELECT*FROM DELETED as posted previously)

In the case of an Insert ;

INSERTINTO dbo.Audit

SELECT*,0FROM DELETED

and a delete ;

INSERTINTO dbo.Audit

SELECT*,2FROM DELETED

Yes you are right, there wouldn't be much point in the checks.

Hunchback, I didn't realise that triggers fired if no rows were affected. I'll try it out. However if no rows were affected then DELETED would be empty and it then does not matter which line in the trigger is caught because nothing will go into Audit, except for the needless execution. Tell me, does ; exists(select * from inserted) stop as soon as it finds one occurrence or does it actually evaluate the entire content of bracket ? i.e. does it actually select all of the records or does it stop as soon as it can satisfy the IF statement. If the latter is true then yes your statement is more efficient. Otherwise I will be doing Select * several times such as ;

if exists(select * from inserted) and exists(select * from deleted) --Update

if exists(select * from inserted) and not exists(select * from deleted) --Insert

if not exists(select * from inserted) and exists(select * from deleted) --Delete

I think we've kicked this one to death but I will include the use of ROWCOUNT and yes you both are right that extra work is undesirable no matter how small an impact we think it is going to have.

|||

Hi Preet,

It will stop as soon as it finds something. You can assign a value to a variable in case of existece so you do not need to inquiry again.

if @.@.rowcount = 0

return

declare @.i int

declare @.d int

if exists (select * from inserted)

set @.i = 1

else

set @.i = 0

if exists (select * from deleted)

set @.d = 1

else

set @.d = 0

if @.i = 1 and @.d = 1 then -- update

...

if @.i = 1 and @.d = 0 then -- insert

...

if @.i = 0 and @.d = 1 then -- delete

...

AMB

How to deploy a single package of a multiple package solution

Hi,

I have a multiple package solution that I've deployed using the manifest file produced with the development environment. If I need to make a change to a single package, how do I then deploy this package? Is it a case of rebuilding the entire solution and re-running the manifest file, or is there a simpler way?

Any help would be much appreciated, cheers.

Can't you just go into the bin folder of your solution and select the appropriate package then copy this to your desired location?|||

I've experienced some strange behaviour doing this in the past that was solved by redeploying the entire solution using the deployment wizard. So I wasn't sure if there was something going on with the registry that I wasn't appreciating.

Are you confident that simply copying a modified package to the SSIS package store location will work consistently?

|||

To be perfectly honest, I've never used the manifest or deployment utility. All I ever do is copy the package from the bin folder to my release folder. I then use the SQL package store and import from my release folder (although you can do this from your development directory, I like to keep one additional layer of last good build that I can reimport to the sql package store if worst comes to worst).

I do not believe that there is anything modified in the registry at any point in time during package deployment. I could very well be wrong, but from what I have read that is not the case. (NOTE: there is the possibility that you are using registry configurations, which you will need to set up in your new location)

-- From Microsoft SQL Server 2005 Integration Services by Kirk Haselden

"Integration Services provides a utility for moving packages, butfor a moment, let's take a step back and think about the deployment problem. What is it you're trying to accomplish? Is there something in the package, some setting or variable that can't be moved by simply copying the package to another machine? Not really. However, problems arise when you move a package that references external resources that are available on one machine that aren't available on another. For example, no amount of configuration magic is going to help if you attempt to run a package that references a custom task that isn't installed on the destination machine."

|||

Ah, maybe that is the answer, simply use the Import option to load modified packages to the SSIS package store. Thanks for taking the time to reply, much appreciated.

How to delete repeated entries from table using T-SQL statement

Hi Friends..

I want delete repeated entries which comes twice in a table. How to delete that extra entry and keep each single entry using T-SQL statement(SQL server 2000). Please give me the example.

Thanks & Regards,
Ravi.Hi,

You may select distinct the duplicate entry and save it in a temporary table, then delete the double entry to the main table and insert the content of the temporary table to the main table.|||Hi,

Thank you for giving solution, but still I don't know how to do that, can you send me code and e.g. It will help me for understanding. I hope you will give this solution very soon.

Thanks & Regards,
Ravi.

How To Delete All Tables

Hi All,
How can i delete all the tables in a DATABASE with a single shot!!
Thanx in advanceWell...what i do is:) may not be the right way:))

Just delete the database......and create a new database......else it will start asking for so many dependencies:))

cheers!


Originally posted by Saravanan.R
Hi All,

How can i delete all the tables in a DATABASE with a single shot!!

Thanx in advance|||Benny,

In a database TABLES, VIEWS, FUNCTIONS, PROCEDURES, TRIGGERS, etc. are there! So I have to delete only TABLES. If I drop the database means I want to recreated all those objects.

I need to delete TABLES alone.

Thanx in advance|||another simple way

generate SQL script of the database, select "all tables", select "generate drop command for each object" check box only (not the create one). you should have a script with all drop commands. run it|||Hi

Its not a appropriate way to drop! plz..|||DELETE FROM sysobjects WHERE xtype='U'
Need to check the option "update system catalogs" for the server properties prior to execute the statement.|||Originally posted by Saravanan.R
Hi All,

How can i delete all the tables in a DATABASE with a single shot!!

Thanx in advance

DO NOT delete anything from sysobjects. That's insane.

If you want to delete the tables (in SQL Server lingo, this just means delete the data out of all of them).

DECLARE
@.sql VARCHAR(4000),
@.int_counter INT,
@.int_max INT

DECLARE @.tables TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
table VARCHAR(256))

INSERT @.tables(table)
SELECT name FROM sysobjects WHERE xtype = 'U'

SELECT
@.int_counter = 1,
@.int_max = (SELECT MAX(ident) FROM @.tables))

WHILE @.int_counter <= @.int_max
BEGIN

SELECT @.sql = 'DELETE ' + table
FROM @.tables WHERE ident = @.int_counter

SELECT @.int_counter = @.int_counter + 1
END

This will only work if you don't have foreign keys though, so you would have to make a similar procedure to drop and recreate those. You can find one on SQLServerCentral.com though.

If you really want to just drop all the tables, change the DELETE to DROP TABLE, and you are good to go. NEVER edit the system tables as a shortcut. It's dangerous; and there are too many good scripts someone else has already written to be doing that.|||"Its not a appropriate way to drop!"???

What they heck WOULD be an appropriate way to drop all the tables in a database with dependent procedures, views, functions, and perhaps even triggers?

I have trouble believing what you are doing is appropriate or necessary in the first place!|||Originally posted by blindman
"Its not a appropriate way to drop!"???

What they heck WOULD be an appropriate way to drop all the tables in a database with dependent procedures, views, functions, and perhaps even triggers?

I have trouble believing what you are doing is appropriate or necessary in the first place!

If he's dropping the tables to just recreate them, the biggest problem will be the foreign keys if he has them.|||He is just askin to delete ALL tables. Why r u scared of the foreign keys, obviously he would have the script for recreating the tables which would include the relationships too.|||Again, What For?

If he can run a script to restore them, presumably he could run upalsen's script solution to drop them...|||He says that he wanna delete all tables in one shot, just for fun i guess, therefore one delete query in sysobjects would satiate his desires rather to select all tables and then choosing include drop tables statement and then running the drop table commands for each table.
However, it's clear that playin with system catalogs is not so wise. U r right indeed.|||Saravanan.R

why do you want to do this
it may be that we may have an alternate solution for your problem other than deleting all of your tables

for example if you want to just remove all of the data from your tables without dropping them, then try the truncate table statement.

[Books Online] Truncate Table

ps if you ever directly modify a system table, we will run you out of town and burn your castle like a group of villagers chasing the frankestein monter|||Hi All,

Sorry I have to DELETE (DROP ALL TABLES) in a single shot!|||You think he wants to drop all the tables in his database "just for fun"?

What the heck are YOUR hobbies?|||You can drop all the tables in various different ways. The solution that is right depends on what you are trying to achieve by dropping the tables.

What is it you are trying to achieve? And please don't say "I have to DELETE (DROP ALL TABLES) in a single shot!", we want to know why...|||Sorry Blindman,

After migrating the Oracle to SQLServer I have to compile the SQLServer Objects in a Database. While compiling the Object I will shows error! bec' already Objects were created! Instead of that, before compiling it I want to DROP ALL TABLES. I have thousands of table in my DATABASE. SO I need this statement IF POSSIBLE!. I don't want to DROP THE DATABASE , Thats why am put Q in forums is there any anternate way to DROP TABLES.?

Its not a hobby to post question in FORUMS like this! Sorry Blindman!!.|||Ok, i think there could be some scenarios where one may need to use system-catalogs, e.g, if i need to drop the tables in a DB with names DELETED at the end and i've more that hunderds of table in the database.
What would i do according to derrickleggett's wise advise that i would go to enterprise-manager, make a script of creation and deletion of these tables by looking at each table and marking only the DELETED tables to be included in the script, right. After spending a lot of time i would create the script and then go to query-analyzer and run the DROP TABLES section for these tables. How safe is it, but took very much time, right?
But if i know what i wanna do, i would rather choose:
DELETE FROM sysobjects WHERE name like '%DELETED' AND xtype='U'

It would delete the tables in ONE SHOT in few seconds.
As mentioned; although it's not a good technique to play with sys-catalogs but sometimes there's no other way out.
Now one can comment that why do i need to delete all the DELETED tables in ONE SHOT:D

Howdy!!|||You can always experiment with sp_msforeachtable, but really, you NEED a script that clears the database. In fact, if this is "your baby" you need a good dozen or two of db maintenance scripts, including dropping, creating, etc. You have to be a lazy DBA to have good stuff for everything, but you can't be "that" lazy...|||OK, I guess I understand why you want to do this (thousands of tables? That's a whole other problem...). But why must it be a single statement? I don't see anything in your requirement that prevents you from running multiple statements that load a list of tables into temporary storage and then loops through issueing dynamic DROP statements. This would seem to be what you want, but might require a half-dozen different statements to set up the temporary table(s), populate them, and then run your DROP loop.|||Actually the problem is:
Saravanan.R wants it in ONE SHOT. That's it.:o|||If that was the case, upalsen's script solution would work fine. It may be a long script, but it would be executed "in one shot". :o :rolleyes:|||Hmmm, I want a house and a boat in Dominican Republic...Anyone has a script so that I can get it "IN ONE SHOT"??!!

Also, if DRI is present, along with a script that generates DROPs for tables Saravanan.R will have to handle removing FK constraints.

Here's how I'd do it:

- Create a view using the following code:

create view dbo.vw_DropTables (stmt) as
select
'alter table ' + object_name(id) + ' drop constraint ' +
object_name(constid) + char(13)+char(10)+
"if @.@.error != 0 raiserror ('Failed to drop " +
object_name(constid) + " constraint!', 15, 1)"+
char(13)+char(10)+'go'
from sysconstraints where objectproperty(constid, 'IsForeignKey') = 1
union
select 'drop table ' + name + char(13)+char(10)+
"if @.@.error != 0 raiserror ('Failed to drop " +
object_name(id) + " table!', 15, 1)"+
char(13)+char(10)+'go'
from sysobjects where objectproperty(id, 'IsMSShipped') = 0
and objectproperty(id, 'IsTable') = 1
go

- Create a batch file with the following commands:

bcp <your_db_name>.dbo.vw_DropTables out DropTables.SQL
-S <your_server_name> -T -c
if not exist DropTables.SQL goto ErrorHandler
osql -S <your_server_name> -E -Q"DropTables.SQL" -b
if errorlevel 1 goto ErrorHandler
exit

ErrorHandler:
echo ERROR!|||insert into AccountBalances (AccountHolder, Balance)
select 'rdjabarov', sum('Balance')
from AccountBalances

You owe me a maragarita.|||Mr. Lindman,

I didn't quite get your reply. But that may be the language barrier, huh?!

...And your query will bomb:

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an argument.

You owe me a maragarita.
Never heard of such drink...Is it popular in OH?|||This script will work also:

DECLARE
@.int_counter INT,
@.int_max INT,
@.txt_type CHAR(1),
@.txt_object VARCHAR(256),
@.txt_object_parent VARCHAR(256),
@.txt_sql VARCHAR(4000)

DECLARE @.objects TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
object VARCHAR(256),
object_parent VARCHAR(256),
object_type CHAR(1))

INSERT @.objects(
object,
object_parent,
object_type)

SELECT CONSTRAINT_NAME, TABLE_NAME, 'C'
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
UNION ALL
SELECT name, '','U'
FROM sysobjects
WHERE
xtype = 'U'
AND name NOT LIKE 'dt_%'

SELECT
@.int_counter = 1,
@.int_max = (SELECT MAX(ident) FROM @.objects)

WHILE @.int_counter <= @.int_max
BEGIN

SELECT
@.txt_type = o.object_type,
@.txt_object = o.object,
@.txt_object_parent = o.object_parent
FROM
@.objects o
WHERE
o.ident = @.int_counter

IF @.txt_type = 'C'
BEGIN
SELECT @.txt_sql = 'ALTER TABLE ' + @.txt_object_parent + ' DROP CONSTRAINT ' + @.txt_object
END
ELSE
BEGIN
SELECT @.txt_sql = 'DROP TABLE ' + @.txt_object
END

PRINT @.txt_sql

SELECT @.int_counter = @.int_counter + 1
END

and, Talat this isn't a wrestling match to show who's the most manly man in the room. It's just a forum to help people. If you delete all the tables from sysobjects, you just messed up the entire database. You didn't do anything in one shot except shoot yourself in the head.

People should rarely mess with the system catalog, especially not in your case when it's obvious you have no idea how they work. Do you have any useful script to post?|||Good lord, I even had syntax errors in my drink order.

Bartender, call a cab to drive me home.|||use DominicanRepublic
go
exec sp_changeobjectowner 'house', 'rdjabarov'
go
exec sp_changeobjectowner 'boat', 'rdjabarov'
go|||Hi All,

DROP TABLE works with " derrickleggett " solution.

Thanx to all.|||Good for you! Why don't you hire him? He'll provide you with solutions!|||You don't have to hire me. Just send me a check.|||I wouldn't. At a minimum your script will drop dtproperties, and the result still needs to be dealt with (copy/paste into another script). And what's up with looping? Didn't you see BCP...OUT posted earlier?|||I'm just amused by the idea of India outsourcing to Kansas City, MO.|||Originally posted by rdjabarov
I wouldn't. At a minimum your script will drop dtproperties, and the result still needs to be dealt with (copy/paste into another script). And what's up with looping? Didn't you see BCP...OUT posted earlier?

Yeah, that's because I typed AND name NOT LIKE 'dt_%' instead of AND name NOT LIKE 'dt%'. You could have just said that instead.

Also, if you change the PRINT @.txt_sql to EXEC(@.txt_sql) it will run it instead of printing it. I figured most people would figure that out.

How can i delete all the tables in a DATABASE with a single shot!!
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That's the original post and all he's really trying to do.

The looping loops through those @.txt_sql statements and either prints them off or executes them one by one. It starts at 1 and goes until it gets to the end. It does this by using WHILE and incrementing a counter variable.|||If his company hires the smart chap, this fellow will be fired:)|||Hi derrickleggett,

Yah! In the place of PRINT I replaced the EXEC Statement to EXECUTE that. It works perfectly.

I think that, We can use to DELETE all PROCEDURE instead of putting 'P' for 'U'.|||Yeah, if you want to do that though, just edit it to except an object type, so you can pass in the object type to it.|||Hi,

I have modified to DELETE all the PROCEDURES, VIEWS & now am trying to do function too.|||Originally posted by Saravanan.R
Hi,

I have modified to DELETE all the PROCEDURES, VIEWS & now am trying to do function too.

WHERE xtype IN ('IF','FN','TF')

Why not just drop the whole database. lol|||...or reformat the drive. That's an easy way to drop all tables, procedures, views, and functions in all databases IN ONE SHOT!|||Hi Blindman,

have u forgot the article that i was posted on 04-05-04 10:58 !!!!!!!???

Plz read the article i was posted on 04-05-04 10:58

Thanx for all|||Hey ... just tell us what the hell are you trying to do ... we all are pretty confused ...|||Hi All,

I want to Drop Tables, Procedures, Views ... Because

after migrating the Oracle to SQLServer I have to compile the SQLServer Objects in a particular Database(Eg.sample). While compiling the Objects, if already Objects were exists! I will shows the error! For this I want to drop objects in my DATABASE.

have u got it!!!|||I still don't get it ...

I think I am getting more confused with the terminology you are using ... can you explain it in simpler words ??|||Originally posted by derrickleggett
Yeah, that's because I typed AND name NOT LIKE 'dt_%' instead of AND name NOT LIKE 'dt%'. You could have just said that instead.

Also, if you change the PRINT @.txt_sql to EXEC(@.txt_sql) it will run it instead of printing it. I figured most people would figure that out.

How can i delete all the tables in a DATABASE with a single shot!!
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That's the original post and all he's really trying to do.

The looping loops through those @.txt_sql statements and either prints them off or executes them one by one. It starts at 1 and goes until it gets to the end. It does this by using WHILE and incrementing a counter variable. Hey, I understand your code, no need to explain what WHILE loop does and what it starts with ;) I don't think you understood mine though! But hey, I've done your loops and got away from it, because there are more elegant ways to do it.

And I do agree with others who question this "DROP IN ONE SHOT" thing. I assume he has the schema script as well, the one that does the reverse, - CREATE ALL OBJECTS IN ONE SHOT...Can you do that? I can ;) But having this knowledge completely dismisses the DROP-ALL approach as absolutely inadequate, regardless of what you're moving your database from, - Oracle, Horacle, Shmoracle, and everything else :D|||Originally posted by Enigma
I still don't get it ...

I think I am getting more confused with the terminology you are using ... can you explain it in simpler words ??

It sounds like part of his import process is validating the actual compiling of objects as a validation process. This actually makes quite a bit of sense.

For instance, if I want to import one subject area at a time, I test the import of those pieces in stages. The first step is to go to my sample database and delete those objects if they exist from a previous test. I only wan to delete the objects I'm testing and don't want to recreate the database.

I then test the compiling of those objects. I then test the import of the data. After the tests validate my process, I run it on the server I'm importing to.

I'm not sure if this is exactly what he's doing, but I could understand why he would want to do it after doing data conversion for so many years. The more validation you have the better.|||Originally posted by blindman
...or reformat the drive. That's an easy way to drop all tables, procedures, views, and functions in all databases IN ONE SHOT!

DELETE * FROM sysdatabases.
It's more easy!!!!!!!:p|||Talat, if you ever come near my server room I will have you arrested. lol|||Hi Enigma,

I have more than 500tables, procedures .. in my SQLServer DATABASE ( Example: saravanan). I have to fix error in my SQL-Server Tables, Transaction SQL Codes afte migrating Oracle to SQLServer. While compiling SQLServer Tables, Procedure.. On that time If the tables, procedures already created in my SARAVANAN database. It will show errors like this " There is already an object named 'TEMPFUNC' in the database. "

For this purpose I have to drop all the tables , procedures before my compilation.|||Anbody know where I can get my Shmoracle certification? I hear it's pretty tough.|||Which track u r interested in, sir, i complted OCP-Developer a few months back and pursuing DBA track now. Both r rather easy. I'm gonna give 70-228 on 20th of April, wish me good luck plz u all, especially Sarvanan!!|||Hi TALAT,

DO THE BEST.

Luv
Saravanan.R|||Originally posted by blindman
Anbody know where I can get my Shmoracle certification? I hear it's pretty tough. I am developing a class for Horacle now. Hoping to complete it by this weekend and start with Shmoracle one.|||Saravanan, I still think you may be going about this in the wrong way. If you are getting errors saying a particular object already exists when you run your script, then your script should either:

1) Drop each object before attempting to create it.
or B) Check to see whether the object already exists before attempting to create it.

Dropping all the objects is a rather blunt instrument for the operation, IMHO.|||Lindman does objects...|||Originally posted by blindman
Saravanan, I still think you may be going about this in the wrong way. If you are getting errors saying a particular object already exists when you run your script, then your script should either:

1) Drop each object before attempting to create it.
or B) Check to see whether the object already exists before attempting to create it.

Dropping all the objects is a rather blunt instrument for the operation, IMHO. ...and finally III) Check to see if object exists. If it does, - drop it. And then create it or whatever other sick things you have in mind ;)
D) Do a separate script that has to be based on dependency between objects. Another way of going about doing it is either dropping FK constraints or disabling them. But either way, you need to do it in a thorough, consistent, and error-preventive way, not in a "ONE SHOT" fashion.|||rdjabarov does posts|||Robert does posts!|||yes ofcourse i do.

Thanx all.