Systems Engineering and RDBMS

Archive for March 18th, 2008

TechNet SQL Server 2005 Script Repository

Posted by decipherinfosys on March 18, 2008

In case you have not looked at the technet SQL Server 2005 Script Repository, we would strongly recommend you to do so. You can access the scripts using this link:

http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true

Not only are the scripts a good way of troubleshooting issues, they are an excellent way of learning the DMVs, DMFs and the internals of SQL Server.

Posted in SQL Server | Leave a Comment »

Microsoft Office Communications Server 2007

Posted by decipherinfosys on March 18, 2008

I don’t know how many of you are aware of this but MSFT has a product called “Microsoft Office Communications Server 2007” which can be used for managing all real time communications like IM (Instant Messaging), Audio, Video conferencing. In this day and age as people work together outside of the office boundaries and at times even across continents, this might be something that your company or your client might be interested in evaluating for your tele-communications or collaboration needs. You can read up more on this from the MSFT site for UC (Unified Communications):

http://www.microsoft.com/uc/products/ocs2007.mspx

Posted in Technology | Leave a Comment »

Trigger Basics (Oracle)

Posted by decipherinfosys on March 18, 2008

Triggers are one of the widely used database objects. In Oracle, we can define triggers on tables, views, schema or a database. On tables, we can define DML (insert, update ,delete) triggers, on schema we can define DDL triggers and logon/logoff event triggers, and system event triggers(startup/ shutdown) on the database. In general based on where a trigger is defined it can be for any DML events, DDL events or login events. Common use of triggers are to generate running numbers before inserting new rows, for security restrictions, for populating some other columns based on the new or old column values and for auditing purpose to name few.

Following are the different trigger types for DML related triggers:

1) BEFORE and AFTER triggers: Indicates whether trigger should be fired before or after the INSERT, UPDATE or DELETE operation occurs on the table. It can be used for DDL statements as well but in that case trigger should be defined either on the schema or the database.

2) ROW and STATEMENT triggers: Defines whether trigger should be fired for each row affected or just once for triggering statement:
a) BEFORE ROW: For each row before triggering statement is executed.
b) AFTER ROW: For each row after triggering statement is executed.
c) BEFORE STATEMENT: Once, before triggering statement is executed.
d) AFTER STATEMENT: Once, after triggering statement is executed.

3) INSTEAD OF triggers: These triggers are normally defined on complex view. It allows to update the views which are not directly updatable through DML statements. It updates the underlying table used to create view.

In 11g, Oracle introduced COMPOUND triggers in which, more than one type of trigger can be defined in a single trigger. In compound trigger, we can define specific action for each type mentioned above but all these actions are composed in a single trigger. We will cover more about compound triggers in future blog post.

We can define more than one trigger of different type on the same table. Stretching it further we can even have multiple triggers of the same type. e.g. two before insert trigger on the same table.

When two or more triggers are defined on the table, they are fired in following order.

• All before statement triggers
• All before row triggers
• All after row triggers
• All after statement triggers.

If multiple triggers are of the same type, then they follow above order but there is no guarantee which one will be executed first unless it is created with ‘FOLLOWS’ clause. This clause is introduced in 11g. To know more about this clause, please visit our previous blog post.

By default when triggers are created, they are enabled. One can disable it using ‘ALTER TRIGGER’ command. But in Oracle 11g, we can create trigger in ‘DISABLE’ state. We have covered it in one of our blog post.

It is advisable to avoid creating triggers for defining referential integrity constraints. If triggers are not defined correctly, it may result into mutating table errors. We will cover more about it in our future blog post.

Posted in Oracle | 2 Comments »