What are the types of triggers?

There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and

ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.

:- Lalit Bhutka

Describe the use of %ROWTYPE and %TYPE in PL/SQL

%ROWTYPE: It is responssible to declare a variable as tables structure type where as

%TYPE: it is responssible to declare a variable as tsbles column type.

:- Lalit Bhutka

Linked Server Transaction error Msg 7395, Level 16, State 2, Line 273

Linked Server Transaction error Msg 7395, Level 16, State 2, Line 273

If you are using nested transaction in your stored procedure you will get the same error. Solution to this error is to SET XACT_ABORT  TO ON;  in your stored procedure.

:- Lalit Bhutka

SET NOCOUNT ON Improves SQL Server Stored Procedure Performance

Problem

One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible. As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle. This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.

Solution

Sometimes even the simplest things can make a difference. One of these simple items that should be part of every stored procedure is SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNToption. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.

----------------------------------------------------------------------------------------------------------------

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name 
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>' 
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

CREATE PROCEDURE <procedure_name, sysname, proc_test> 
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 
<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
SELECT @p1, @p2
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
GO

----------------------------------------------------------------------------------------------------------------

Here is the new template style available in SQL Server 2005 with the SET NOCOUNT ON.

----------------------------------------------------------------------------------------------------------------------

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

---------------------------------------------------------------------------------------------

As you can see even simple little things such as this can make an overall improvement for your 

database environment. Stay tuned for other simple tricks and techniques to improve performance.

BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN

When creating a SQL Statement by default, for example, SELECT * FROM HumanResources.Employee, SQL Server will run this statement and immediately return the results:

If you were to add BEGIN TRANSACTION (or BEGIN TRAN) before the statement it automatically makes the transaction explicit and holds a lock on the table until the transaction is either committed or rolled back.

BEGIN TRANSACTION marks the starting point of an explicit, local transaction. - MS

For example, when I issue a DELETE or UPDATE statement I always explicitly use BEGIN TRAN to make sure my statement is correct and I get the correct number of results returned.

Let’s say I want to UPDATE the Employee table and set JobTitle equal to 'DBA' where LoginID is like '%barbara%'. I accidentally create my statement wrong and issue the statement below which actually would make every JobTitle equal to 'DBA':
----------------------------------------------------------------------

 UPDATE HumanResources.Employee
        SET JobTitle = ‘DBA’
        WHERE LoginID IN (
        SELECT LoginID FROM HumanResources.Employee)

----------------------------------------------------------------------

Oops! I didn’t mean to do that!! I accidentally made every record have a JobTitle of DBA. If I would have placed a BEGIN TRAN before my statement I would have noticed that 290 results would be effected and something is wrong with my statement:

Since I specified a BEGIN TRAN, the transaction is now waiting on a ROLLBACK or COMMIT. While the transaction is waiting it has created a lock on the table and any other processes that are trying to access HumanResources.Employee are now being blocked. Be careful using BEGIN TRAN and make sure you immediately issue a ROLLBACK or COMMIT:

As you can see, SPID 52 is getting blocked by 54.

Since I noticed something was terribly wrong with my UPDATE statement, I can issue a ROLLBACK TRAN statement to rollback the transaction meaning that none of the data actually changed:

ROLLBACK TRANSACTION rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. It also frees resources held by the transaction. - MS

If I had written my statement correct the first time and noticed the right amount of results displayed then I could issue a COMMIT TRAN and it would execute the statement and my changes would be committed to the database:

COMMIT TRANSACTION marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active. - MS

25 Best One Minute Party Games

We have a collection of 25 one minute games that are most popular and most liked by our readers for their parties. All these games can be played with things that can be easily arranged at home.

1. Rice and Buttons

An interesting one minute party game to be played with rice and buttons in which players have to pick the buttons from rice bowl. This game can be played with adults and kids on any event. Check out rice & buttons kitty party game…

Number of People Required To Play The Game

No restriction,as many as you want

Things required

• One large bowl filled with rice
• An empty small bowl
• Approx. 50 buttons of white and black

How to Play

• Mix all buttons with rice in the bowl.
• Each player will get one minute to pick buttons from the rice bowl.
• 10 points will be credited for each white button, 5 points will be credited for each black button.
• 5 points will be deducted for each rice grain spilled outside the bowl while picking buttons.

Winner

Player with maximum points.

Had you ever imagined that we can play such an interesting game with rice and button. This is one of the most popular game in our neighbourhood and we play it on any event. Hope you will also enjoy playing this game with your family & friends. This game can also be played with kids. Do not forget to share your experience with us 

*********************************************************************************************

2. Floating Brinjals

One minute party game in which players have to pick the brinjals floating in a water bowl with toothpick. An interesting game that can be played with kids or adults. Check out floating brinjals kitty party game…

Things required

• One big bowl filled with water
• Toothpicks
• Small brinjals

How to Play

• Put all the brinjals in the bowl filled with water.
• Each player gets one minute to prick the floating brinjals with toothpick.

Winner

Player who has pricked maximum number of brinjals with toothpick.

I personally don’t like brinjals but this game is awesome, trust me. Hope you will have lots of fun playing this game. Do stop by to let us know your experience. 

*********************************************************************************************

3. Placing Coins on Pencil

One minute party game in which players have to balance maximum coins on the flat surface of a pencil. Check out placing coins on pencil kitty party game…

Things required

• One new Pencil with flat head
• 20-25 one rupee coins of same size
• A stand to fix the pencil tightly e.g. thermocol

How to Play

• Pencil has to be fixed in the stand.
• Each player has to put one rupee coin one by one on the flat portion of the pencil

Winner

Player with maximum no. of coins placed on the flat surface of pencil.

This is an interesting game to be played not just in kitty parties but also with kids in birthday parties. Hope you will enjoy playing this game. Do not forget to share your experience with us

*********************************************************************************************

4. Playing With Locks & Keys

One minute party game with locks and keys. Check out playing with locks & keys kitty party game…

Number of People Required To Play The Game

No restriction,as many as you want

Things required

• 10-15 locks and their keys.
• 5-6 extra keys for creating fun and confusion.

How to Play

• Lock all the locks and mix all the keys.
• Player has to  find the right key to unlock the locks.

Winner

Player with maximum no. of unlocked locks in one minute.

*********************************************************************************************

5. Tricolor Plastic Balls

One minute tricky party game to be played with plastic balls of 3 different colors. Check out tricolor plastic balls kitty party game…

Number of People Required To Play The Game

No restriction,as many as you want

Things required

• One container filled with small plastic balls
• Plastic balls should be of 3 different colors (20 no. of each color)
• Three empty containers
• One spoon

How to Play

• Each player will pick one ball with the help of spoon from the first container and shift the ball to the second container and from second container to third one.
• While shifting from second to third it should be ensured that the middle container is left with two balls of different color . If it is less then two balls, marks will be deducted as decided by the host
• Time Limit one minute

Winner

Player with maximum no. of balls in the last container.

A little tricky and mind boggling party game but it sure is fun to play with friends and family. This game can also be played with kids. Hope you enjoy playing it…!!

*********************************************************************************************

6. Straw & Thermocol Balls

One minute party game in which players have to suck the thermocol balls using a straw and transfer it to another container. Check out straw & thermocol balls kitty party game…

Number of People Required To Play The Game

No restriction,as many as you want

Things required

• One container filled with small thermocol balls
• One empty container
• 1-2 packs of straws

How to Play

• Each player has to suck the thermocol balls with the help of straw and shift it to the empty container
• While shifting  the balls from one container to another, if the ball falls down, it will not be counted
• Time Limit one minute

Winner

Player with maximum no. of thermocol balls shifted to the empty container

*********************************************************************************************

7. Save The Royal Family

One minute party game in which players have to blow off all the cards and save only king, queen and jack. Check out save the royal family kitty party game…

Number of People Required To Play The Game

No restriction,as many as you want

Things required

• One deck of Playing cards
• One Tray

How to Play

• Place  the King, Queen, Jack  of any one colour facing upwards on the tray
• Spread the rest of the cards with face down covering those three cards.
• Player has to blow the cards in such a way that only three cards facing upwards are left in the tray
• Time limit is one minute

Winner

Player  who complete the task in one minute is the winner. 

This game can be played with kids and adults on any occassion. Do share your experience with us if you choose to play this game at your party 

*********************************************************************************************

8. Pull & Pluck

One minute party game to be played with cloth pins. Check out pull & pluck kitty party game…

Number of People Required To Play The Game

No restriction.

Things required

• Different colour cloth pins

How to Play

• Clip 10 cloth pins of different colour on each player’s clothes at the back
• After the time starts, each player has to get each other’s cloth pins and clip it on their clothes at the front
• Time limit is one  minute

Winner

Player who has the maximum/same colour clips clipped on their clothes at the front wins.

This really a great party game and guests enjoy a lot playing these. You can hos this game for kids on birthday parties as well as for adults on any occassion. Let us know if you enjoy playing this game…!!  .

*********************************************************************************************

9. Fun With Bangles & Candle Wax Drop

One minute party game to put bangles on candle wax. Check out bangles & candles wax drop kitty party game…

Number of People Required To Play The Game

No restriction, as many as you want. At a time only one player will be playing the game.

Things Required

• Candle
• Matchbox
• 10 to 20 bangles(glass bangles)
• Wooden board

How To Play

• Light up the candle & put a wax drop on the card board.
• Stick the bangle on the wax drop.
• The bangles must stand on the board with the help of wax.
• If any bangle drop down, it is not counted.
• Time limit is one minute.

How To Decide Winner

Person who places maximum number of bangles in one minute

*********************************************************************************************

10. Matchstick Equations

One minute party game to make equations with matchsticks. Check out matchstick equations kitty party game…

Number of People Required To Play The Game

No restriction,as many as you want

Things required :

• One match box

How to Play

Player has to write below equations with the help of matchsticks in one minute.

• XX – VIII = XII  – 16 Matchsticks
• XIX – XII = IX – 15 Matchsticks
• X – VIII = II – 12 Matchstick
• VII – II = V – 11  Matchstick
• VVIII -III = VV -17 Matchstick
• If time left player can start writing  again

Winner

The Player who has written the maximum no. of equations.

*********************************************************************************************

11. Color Or Word

One minute party game that will confuse the participant whether to say the word or the color in which the word is written. Check out color or word kitty party game…

Number of People Required To Play The Game

No restriction. More the number of guests, more fun it is going to be.

Things Required

• One sheet  with following phrases.
• RED KITTY PARTY GAMES
• BLUE ONE MINUTE PARTY GAMES
• YELLOW COUPLE PARTY GAMES
• GREEN VALENTINE GAMES
• PURPLE  ICE BREAKING GAMES
• ORANGE ONE MINUTE PARTY GAMES

How To Play

• Call the guests one by one and ask them to read the paper.
• You can add more lines or these can be repeated.
• They have to speak the colors used for writing these sentences.
• For eg. instead of reading  ‘RED KITTY PARTY GAMES’ they have to read ‘GREEN PURPLE BLUE ORANGE’
• Time Limit is one minute.

Winner

The player who reads maximum correct lines on one minute wins.

*********************************************************************************************

12. Sieve & Pins

One minute party game to put maximum pins in bangle through the sieve. Check out sieve and pins kitty party game…

Number of People Required To Play The Game

No restriction, as many as you want.

Things Required

• A large sieve (one used for sieving wheat grains)
• A pack of ‘all pins’ (also known as all purpose pins)/ Needles
• One bangle
• One large plate

How To Play

• Place the bangle on the plate and cover it with the sieve inverted.
• Shake the plate so that bangle also moves within the sieve. By doing this position of the bangle on the plate will remain unknown.
• Each participant has to put ‘all pins’/needle through the sieve holes.
• Time limit is one minute.

How To Decide Winner

Participant who puts the maximum number of pins/needle within the bangle in one minute is the winner.

*********************************************************************************************

13. Tie The Knot

One minute party game to tie maximum knots on rope. Check out tie the knot kitty party game…

Number of People Required To Play The Game

No restriction,as many as you want

Things required

• A long rope

How To Play

• Tie as many knots as you can  on the rope
• Time limit is one minute

Winner

Player who has tied maximum knots on the rope in one minute.

*********************************************************************************************

14. Blow The Ball

One minute party game in which participants have to blow the ball using a straw. Check out blow the ball kitty party game…

Things Required

• Straws – as many as number of kids
• Small thermocol balls
• A long table

How To Play

• Draw starting and finishing line on the table.
• Each participant will get one minute to blow a thermocol ball with the help of a straw.
• If thermocol ball falls out of the table, he/she will be considered OUT.

Winner

Player who will be able to blow the thermocol ball till the finishing line in one minute.

*********************************************************************************************

15. Money Money

A fun party game in which each participant has to make a perfect 10 with coins of different denomination. Check out money money kitty party game...

Things Required

• Lots of 1 Rs and  2 Rs coins of different size,design and versions. (old and new coins of various denominations)
• Few 5 Rs coins of latest version.
• If possible, add 50 paise and 25 paisa coins as well.

How To Play

• Mix all the coins in a bowl.
• Ask the players to make sets of coins totalling to ten rupees .
• Time limit is one minute.

Winner

The player who manages to make  the maximum  sets of 10 Rupees with coins is the winner!!

*********************************************************************************************

16. Blow Off The Candle

One minute party game in which each player is blind folded and he/she needs to blow off the candle. Check out blow off the candle kitty party game...

Things Required

• Candles
• Matchbox
• A cloth for blind folding players
• A table

How To Play

• Keep the candle near table’s edge and light it.
• Bring the player in front of candle & blind fold him/her.
• Ask player to take 3 round turns and now he/she needs to blow off the candle.
• Fun part is watching player trying to blow of the candle where it has never been.
• Time limit is one minute.

Winner

Player who blows off the candle in minimum time.

*********************************************************************************************

17. Let’s Draw On Balloon

One minute party game in which each participant needs to draw maximum figures on balloon with a ball point pen. Check out let’s draw on balloon kitty party game…

Things Required

• Balloons
• Ball Point Pens

How To Play

• Give one balloon & a ball point pen to each participant.
• Ask them to blow air in the balloon and make it as large as possible.
• Now ask them to draw as many people as possible on balloon with pen.
• Only complete figures will be counted i.e. figure should have a face, two eyes, two ears, one nose, lips, two hands and two feet.
• Time limit is one minute for drawing the figures.
• Fun part is when participant will pop their balloon with tip of the pen while trying to draw maximum figures.

Winner

Participant who could draw maximum complete figures in one minute.

*********************************************************************************************

18. Pick The Bangles

A one minute party game for kitty parties where each player is given one minute to pick maximum bangles of same colors with knitting needles. Check out pick the bangles kitty party game…

Things Required

• Different color bangles (Minimum 10 bangles of 3-4 colors each)
• 2 knitting needles
• Tray
• Bowl
• Paper chits with colors of bangle written on it

How To Play

• Keep all the bangles in a tray & paper chits in a bowl.
• Participants will play this game one by one.
• Ask participant to pick one of the chit, say he/she picks a chit of green color.
• Now give 2 knitting needles to the player.
• He/She needs to pick only green color bangles with the needles.
• Time limit is one minute.

Winner

Whoever picks maximum bangles in one minute will be the winner.

*********************************************************************************************

19. Roman Numbers

A fun one minute party game where players have to create roman numbers with matchsticks in one minute. Check out roman numbers kitty party game...

Things Required

• Matchsticks or matchboxes as per the number of participants

How To Play

• Give one matchbox to each participant.
• Now each participant has to make roman numbers using the matchstick.
• Time limit is one minute.

Winner

Whoever makes maximum roman numbers with matchsticks in one minute is the winner.

Roman Numbers: I, II, III, IV, V, VI, VII, VIII, IX, X

*********************************************************************************************

20. Pin The Rubber Band

A one minute party game in which players have to make longest chain of safety pins and rubber bands. Check out pin the rubber band kitty party game…

Things Required

• Rubber Bands
• Safety Pins
• 2 Bowls

How To Play

• Keep rubber bands and safety pins in 2 separate bowls.
• Now each participant needs to put 2 rubber band in a safety pin and make a chain as shown in the image above.
• Time limit is one minute.

Winner

Whoever makes longest chain in one minute will be the winner.

*********************************************************************************************

21. Pencil The Bangle

A one minute party game in which each player has to put as many bangles as possible in a pencil to win. Check out pencil the bangle kitty party game…

Things Required

• One sharpened pencil
• 10-15 metal or plastic bangles
• A stand to fix the pencil tightly e.g an eraser/rubber or a small piece of thermocol)

How To Play

• Put the pencil in eraser or piece of thermocol such that it is standing straight.
• Draw a starting line at approximately one feet distance from pencil.
• Now, each player will get one minute to throw the bangles and put in the standing pencil.

Winner

Whoever puts maximum bangle in one minute will be the winner.

*********************************************************************************************

22. Drop The Playing Cards

A one minute party game in which players have to throw maximum playing cards in the bowl over chair in one minute. Check out drop the playing cards kitty party game…

Things Required

• A pack of playing cards
• An empty bowl

How To Play

• Let the player  stand in front of the chair.
• Keep a bowl at the back of the chair on the ground.
• Give a pack of playing cards to the participant
• As the time starts, the player has to  throw the cards one by one into the bowl over the chair.
• Time limit is one minute.
• Count the cards which have fallen inside the bowl.  

Winner

The player who has put maximum cards in the bowl wins!!

*********************************************************************************************

23. Pick The Buttons

A one minute party game in which players have to pick buttons using a safety pin. Check out pick the buttons kitty party game…

Things Required

• 5 Red, 20 black and 40 white color buttons
• A pack of large safety pins

How To Play

• Put all the buttons in a small bowl and mix thoroughly.
• Assign points to each color button e.g. 10 to white, 20 to black and 30 to red.
• Each player has to use a safety pins to pick as many buttons as possible from the bowl.
• Time limit is one minute.

Winner

Host will have to sum up the points of all the picked buttons. Whoever scores maximum will be the winner.

*********************************************************************************************

24. Ulta Ka Fulta With Alphabets

One minute party game to write alphabets and words starting with these alphabets in reverse order. Check out ulta ka fulta with alphabets kitty party game…

Number of People Required To Play The Game

No restriction, as many as you want. It is good to have at least 10 people.

Things required

• Pen and Paper as per the number of participants

How to Play

• Every participant should be given a pen and a  paper.
• Each one should write their name on the paper.
• Host then will ask each participant to write alphabets in backward order and the word starting with that alphabet for example Z….zebra, Y….yark and so on.
• Word with wrong spelling will not be counted.
• Time limit one minute.

Winner

Participant who writes maximum words in one mintue is the winner.

*********************************************************************************************

25. Safety Pins & Rice

A one minute party game in which blindfolded participants have to pick maximum safety pins and minimum rice. Check out safety pins & rice kitty party game…

Things Required

• A bowl filled with rice
• 25-30 small safety pins

 How To Play

• Mix safety pins with rice in a bowl.
• Blindfold the player  and tell him/her to pull out as many safety pins as possible from the bowl and keep them in the plate without spilling the rice.
• Repeat this until every one  had a turn.
• Deduct the number of rice in the plate from the number of safety pins.
• Time limit is one minute.

Winner

The players who has pulled out the maximum safety pins and minimum rice wins.

*********************************************************************************************

Hope you will like all the games – let us know which one you liked the most